package com.iris.live.services.data.repositories.impl;

import com.google.common.base.Strings;
import com.google.common.collect.Maps;
import com.iris.live.services.common.GPUtils;
import com.iris.live.services.data.models.报价单表Model;
import com.iris.live.services.data.repositories.SalesRepository;
import com.iris.live.services.interceptors.SessionInterceptor;
import com.iris.live.services.models.PageData;
import com.iris.live.services.models.RequestBase;
import com.iris.live.services.services.SalesService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import javax.servlet.http.HttpServletRequest;
import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Created by sxy on 2016/8/8.
 */
@Repository
@Transactional // (readOnly = true)
public class SalesRepositoryImpl extends BaseRepository implements SalesRepository {
    // 订单总价计算
    private static final String startOrderSql = "select \n" +
            "\tIFNULL(c.保险总价,0)+IFNULL(c.车船税,0)+IFNULL(i.购置税,0)+IFNULL(d.上牌服务费,0.0)+IFNULL(d.其他服务费,0)+IFNULL(g.金融服务费,0)+   \n" +
            "\tIFNULL(f.延保折后价,0)+IFNULL(e.精品折后价,0)+a.折后价+   \n" +
            "\tIFNULL(h.置换手续费,0)+IFNULL(f.厂家延保实收,0)+IFNULL(f.第三方延保实收,0)+IFNULL(i.代金券,0)+   \n" +
            "\tIFNULL(i.会员费,0)+IFNULL(i.续保服务费,0)+IFNULL(i.贷款抵押费,0)+IFNULL(i.二手车押金,0)+   \n" +
            "\tIFNULL(i.上牌保证金,0)+IFNULL(i.车辆管理费,0)+IFNULL(i.常规保养,0)+IFNULL(i.其他六,0)+   \n" +
            "\tIFNULL(i.其他七,0)+IFNULL(i.其他一,0)+IFNULL(i.其他二,0)+IFNULL(i.其他三,0)+IFNULL(i.其他四,0)+IFNULL(i.其他五,0) 订单总价,\n" +
            "\tb.* \n" +
            "from(\n";

    private static final String orderSql = startOrderSql + "select  \n" +
            "  报价订单表.订单客户姓名 as '订单客户',  \n" +
            "  报价单表.报价编号 as '报价编号',  \n" +
            "  报价单表.初始报价单号 as '初始报价单号',  \n" +
            "  报价单表.销售顾问 as 'sc_email',  \n" +
            "  (select 人员姓名 from 用户 where 用户名 = 报价单表.销售顾问) as '销售顾问',  \n" +
            "  (select 人员电话 from 用户 where 用户名 = 报价单表.销售顾问) as 'sc_cell',  \n" +
            "  (select 电话号码 from 顾客记录汇总 where 顾客编号 = 报价单表.顾客编号) as 'custom_cell',  \n" +
            "  报价订单表.订单号 as '订单号',  \n" +
            "  CASE  \n" +
            "    WHEN NOT ISNULL(报价交车表.交车日期) THEN '交车完毕'  \n" +
            "    WHEN (NOT ISNULL(报价交车表.开票时间) AND ISNULL(报价交车表.退票时间)) OR (NOT ISNULL(报价交车表.开票时间) AND NOT ISNULL(报价交车表.退票时间) AND 报价交车表.开票时间 > 报价交车表.退票时间) THEN '零售'  \n" +
            "    WHEN NOT ISNULL(报价交车表.退票时间) AND 报价订单表.订单失销日期>=DATE(报价交车表.退票时间) THEN '报价' "+
            "    WHEN NOT ISNULL(报价交车表.退票时间) AND 报价订单表.订金类型 = '订金'  THEN '订金'  \n" +
            "    WHEN NOT ISNULL(报价付款详细.应付金额) AND NOT ISNULL(报价付款详细.实付金额) AND ROUND(报价付款详细.实付金额, 4) >= ROUND(报价付款详细.应付金额, 4) AND 报价付款详细.实付金额 > 0 THEN '全款到账'  \n" +
            "    WHEN NOT ISNULL(报价交车表.合同签约日期) THEN '合同'  \n" +
            "    WHEN NOT ISNULL(报价订单表.订单审批通过日期) AND DATE(报价订单表.订单审批通过日期) > IFNULL(报价订单表.订单失销日期,'1970-01-01') AND 报价订单表.订金类型='订金' THEN '订金'  \n" +
            "    WHEN NOT ISNULL(报价订单表.订单审批通过日期) AND DATE(报价订单表.订单审批通过日期) > IFNULL(报价订单表.订单失销日期,'1970-01-01') AND 报价订单表.订金类型='意向金' THEN '意向金'  \n" +
            "    ELSE '报价'  \n" +
            "  END AS '新车销售状态',  \n" +
            "  车型.来源 as '来源',  \n" +
            "  车型.品牌 as '品牌',  \n" +
            "  车型.级别 as '车系',  \n" +
            "  车型.车型 as '车型',  \n" +
            "  车型.车型详细 as '车款',  \n" +
            "  库存表.库存编号 as '库存编号',  \n" +
            "  库存表.生产日期 as '生产日期',  \n" +
            "  库存表.颜色代码 as '颜色代码',  \n" +
            "  库存表.内饰代码 as '内饰代码',  \n" +
            "  库存表.生产号 as '生产号',  \n" +
            "  库存表.车架号 as '车架号（VIN）',  \n" +
            "  库存表.加配配置 as '加配配置',  \n" +
            "  ifnull(库存表.实际msrp含税, 0) - ifnull(库存表.标配msrp含税, 0) as '加配价格（含税）',  \n" +
            "  ifnull(库存表.采购价格含税, 0) as '采购价格（含税）',  \n" +
            "  库存表.采购价格未税 as '采购价格（不含税）',  \n" +
            "  库存表.协商调车款项金额 as '调车协商价格',  \n" +
            "  库存表.标配MSRP含税 as '标配MSRP（含税）',  \n" +
            "  库存表.实际MSRP含税 as '实际MSRP（含税）',  \n" +
            "  库存表.预计到港日期 as '预计到港日期（ETA）',  \n" +
            "  库存表.预计下线日期 as '预计下线日期',  \n" +
            "  库存表.车辆物流状态 as '车辆物流状态',  \n" +
            "  库存表.NBI车辆状态 as 'NBI车辆状态',  \n" +
            "  库存表.预计到店日期 as '预计到店日期',  \n" +
            "  库存表.入库日期 as '入库日期',  \n" +
            "  库存表.出库日期 as '出库日期',  \n" +
            "  库存表.库龄天数 as '库龄天数',  \n" +
            "  库存表.采购批发商 as '采购经销商',  \n" +
            "  库存表.车辆零售状态 as '车辆零售状态',  \n" +
            "  (select 上牌类型 from 报价客户表 where 编号 = 报价单表.客户详细) as '上牌类型',  \n" +
            "  (select 邮箱     from 报价客户表 where 编号 = 报价单表.客户详细) as '顾客email',  \n" +
            "  (select 身份证   from 报价客户表 where 编号 = 报价单表.客户详细 and 身份证类型 in ('身份证', '外籍ID')) as '顾客身份证号码/外籍ID号码',  \n" +
            "  (select 身份证   from 报价客户表 where 编号 = 报价单表.客户详细 and 身份证类型 = '组织结构') as '组织机构代码',  \n" +
            "  (select 首次上牌日期 from 报价置换表 where 置换编号 = 报价单表.置换编号) as '置换车辆上牌年月',  \n" +
            "  (select 置换估价     from 报价置换表 where 置换编号 = 报价单表.置换编号) as '置换估价',  \n" +
            "  报价订单表.订单流水号 as 'Case号',  \n" +
            "  报价订单表.销售类型 as '车辆零售类型',  \n" +
            " (select 账目日期 from 销售财务账目表 where 账目编号 = 销售财务账目.账目编号) as '意向订单生效日期',\n" +   // 第一笔收款的账目日期
            "  if(报价订单表.订金类型 = '意向金', 报价订单表.订金金额+ifnull(报价订单表.订金加减金额,0), '') as '意向金金额',  \n" +
            "  if(报价订单表.订金类型 = '意向金', 报价订单表.无息退款天数, '') as '意向金无息退款天数',  \n" +
            "  报价订单表.资源释放期限 as '释放资源期限',  \n" +
            "  if(报价订单表.订金类型 = '意向金', 报价订单表.订单失销日期, '') as '意向失销日期',  \n" +
            "  if(报价订单表.订金类型 = '意向金', 报价订单表.退订订金金额, '') as '退意向金金额',  \n" +
            "  if(报价订单表.订金类型 = '意向金', 报价订单表.订单修改日期, '') as '意向修改日期',  \n" +
            "  if(报价订单表.订金类型 = '意向金', 报价订单表.订金加减金额, '') as '意向金+/-金额',  \n" +
            "  if(报价订单表.订金类型 = '意向金', 报价订单表.订金加减日期, '') as '意向金+/-日期',  \n" +

            " date_format((select create_time from sale_admin_car_resource_check where id = saleTemp.id),'%Y-%m-%d') as '订单生效日期',\n" + // 第一次资源确认的日期
            "  if(报价订单表.订金类型 = '订金', 报价订单表.订金金额+ifnull(报价订单表.订金加减金额,0), '') as '订金金额',  \n" +
            "  if(报价订单表.订金类型 = '订金', 报价订单表.订单失销日期, '') as '订单失销日期',  \n" +
            "  if(报价订单表.订金类型 = '订金', 报价订单表.退订订金金额, '') as '退订金金额',  \n" +
            "  if(报价订单表.订金类型 = '订金', 报价订单表.退订金转账日期, '') as '退订金转账日期',  \n" +
            "  if(报价订单表.订金类型 = '订金', 报价订单表.订单修改日期, '') as '订单修改日期',  \n" +
            "  if(报价订单表.订金类型 = '订金', 报价订单表.订金加减金额, '') as '订金+/-金额',  \n" +
            "  if(报价订单表.订金类型 = '订金', 报价订单表.订金加减日期, '') as '订金+/-日期',  \n" +
            "  if(报价订单表.订金类型 = '订金', 报价订单表.申请修改原因, '') as '申请修改原因',  \n" +
            "  if(报价订单表.订金类型 = '订金', 报价订单表.撤销订单原因, '') as '撤销订单原因',  \n" +

            "  报价单表.车辆现金折扣 as '车辆现金折扣',  \n" +
            "  报价单表.车辆销售价 as '车辆折后价',  \n" +
            "  ifnull(报价单表.消费税, 0) as '消费税及附加',  \n" +
            "  ifnull(报价单表.折后价, 0) as '车辆销售价',  \n" +
            "  报价单表.折后价 / 1.17 as '车辆销售价(未税)',  \n" +
            "  报价交车表.合同签约日期 as '合同生效日期',  \n" +
            "  报价交车表.合同失销日期 as '合同失销日期',  \n" +
            "  报价交车表.合同修改日期 as '合同修改日期',  \n" +
            "  left(报价交车表.开票时间, 10) as '开票日期',  \n" +
            "  报价交车表.退票时间 as '退票日期',  \n" +
            "  报价订单表.预计开票日期 as '预计开票日期',  \n" +
            "  报价交车表.交车日期 as '交车日期',  \n" +
            "  报价交车表.上牌省 as '上牌省份',  \n" +
            "  报价交车表.上牌市 as '上牌城市',  \n" +
            "  报价交车表.上牌区 as '上牌行政区',  \n" +
            "  报价交车表.上牌地址 as '上牌具体地址',  \n" +
            "  报价交车表.上牌日期 as '上牌日期',  \n" +
            "  报价交车表.牌照号 as '牌照号',  \n" +
            "  报价交车表.开票客户姓名 as '车主/开票人名称',  \n" +
            "  报价交车表.开票客户身份证 as '车主/开票人身份证号码/外籍ID号',  \n" +
            "  报价交车表.开票客户机构代码 as '车主/开票人组织机构代码',  \n" +
            "  报价交车表.开票客户电话 as '车主/开票人电话号码',  \n" +
            "  ' ' as '提车代理人',  \n" +
            "  ' ' as '提车代理人身份证',  \n" +
            "  ' ' as '外籍提车代理人ID号码',  \n" +
            "  报价交车表.售后联系人 as '售后联系人1',  \n" +
            "  报价交车表.售后联系人关系 as '售后联系人1身份',  \n" +
            "  报价交车表.售后联系人电话 as '售后联系人1电话号码',  \n" +
            "  报价交车表.售后联系人地址 as '售后联系人1联系地址',  \n" +
            "  报价交车表.售后联系人微信 as '售后联系人1微信ID',  \n" +
            "  (select 上报厂家     from 库存利润表 where 库存编号 = 库存表.库存编号) as '上报厂家',  \n" +
            "  (select 上报厂家日期 from 库存利润表 where 库存编号 = 库存表.库存编号) as '上报厂家日期',  \n" +
//                "  (select 上报SIMS     from 库存利润表 where 库存编号 = 库存表.库存编号) as '上报SIMS',  \n" + // dch删除字段
//                "  (select 上报SIMS日期 from 库存利润表 where 库存编号 = 库存表.库存编号) as '上报SIMS日期',  \n" + // dch删除字段

            "  ifnull((select cast(sum(随车成本) as decimal(17, 2)) from 报价随车表 where 报价编号 = 报价单表.报价编号 and 随车类型 = '经销商' and 随车部门 = '销售部'), 0) as '经销商随车成本不含精品延保',  \n" +
            "  ifnull((select cast(sum(随车成本) as decimal(17, 2)) from 报价随车表 where 报价编号 = 报价单表.报价编号 and 随车类型 = '经销商' and 随车部门 = '销售部'), 0) as '经销商随车成本',  \n" +
            "  ifnull((select cast(sum(ifnull(成本, 0) * ifnull(数量, 0)) as decimal(17, 2)) from 报价精品详细表 where 编号 = 报价单表.精品 and 折后价 = 0), 0) as '随车精品成本',  \n" +
//                "  ifnull((select cast(sum(ifnull(延保原价, 0)) as decimal) from 报价五星延保 where 延保编号 = 报价单表.延保编号 and 延保折后价 = 0), 0) as '随车延保成本',  \n" +

//                "  ifnull((select cast(sum(随车成本) as decimal) from 报价随车表 where 报价编号 = 报价单表.报价编号 and 随车类型 = '厂家' and 随车名称 = '保险'), 0) as '厂家SI_保险成本',  \n" +
//                "  ifnull((select cast(sum(随车成本) as decimal) from 报价随车表 where 报价编号 = 报价单表.报价编号 and 随车类型 = '厂家' and 随车名称 = '购置税'), 0) as '厂家SI_购置税成本',  \n" +
//                "  ifnull((select cast(sum(随车成本) as decimal) from 报价随车表 where 报价编号 = 报价单表.报价编号 and 随车类型 = '厂家' and 随车名称 = 'SSSC'), 0) as '厂家SI_SSSC成本',  \n" +
//                "  ifnull((select cast(sum(随车成本) as decimal) from 报价随车表 where 报价编号 = 报价单表.报价编号 and 随车类型 = '厂家' and 随车名称 = '旅游礼包'), 0) as '厂家SI_旅游礼包成本',  \n" +
//                "  ifnull((select cast(sum(随车成本) as decimal) from 报价随车表 where 报价编号 = 报价单表.报价编号 and 随车类型 = '厂家' and 随车名称 = 'SD卡'), 0) as '厂家SI_SD卡成本',  \n" +
//                "  ifnull((select cast(sum(随车成本) as decimal) from 报价随车表 where 报价编号 = 报价单表.报价编号 and 随车类型 = '厂家' and 随车名称 = '加油卡'), 0) as '厂家SI_加油卡成本',  \n" +
//                "  ifnull((select cast(sum(随车成本) as decimal) from 报价随车表 where 报价编号 = 报价单表.报价编号 and 随车类型 = '厂家' and 随车名称 = '实物礼品'), 0) as '厂家SI_实物礼品成本',  \n" +
//                "  ifnull((select cast(sum(随车成本) as decimal) from 报价随车表 where 报价编号 = 报价单表.报价编号 and 随车类型 = '厂家' and 随车名称 = '服务礼券'), 0) as '厂家SI_服务礼券成本',  \n" +
            "  ifnull((select cast(sum(随车成本) as decimal(17, 2)) from 报价随车表 where 报价编号 = 报价单表.报价编号 and 随车类型 = '厂家'), 0) as '厂家随车成本总价',  \n" +
            "  ifnull((select cast(sum(随车成本) as decimal(17, 2)) from 报价随车表 where 报价编号 = 报价单表.报价编号 and 随车类型 = '厂家' and 随车名称  <> '购置税'), 0) as '厂家非购置税随车成本',  \n" +

            "  ifnull((select cast(sum(厂家返利) as decimal(17, 2)) from 报价随车表 where 报价编号 = 报价单表.报价编号 and 随车类型 = '厂家'), 0) as '厂家随车实收总价',  \n" +
//                "  ifnull((select cast(sum(厂家返利) as decimal) from 报价随车表 where 报价编号 = 报价单表.报价编号 and 随车类型 = '厂家' and 随车名称 = '保险'), 0) as '厂家SI_保险实收',  \n" +
//                "  ifnull((select cast(sum(厂家返利) as decimal) from 报价随车表 where 报价编号 = 报价单表.报价编号 and 随车类型 = '厂家' and 随车名称 = '购置税'), 0) as '厂家SI_购置税实收',  \n" +
//                "  ifnull((select cast(sum(厂家返利) as decimal) from 报价随车表 where 报价编号 = 报价单表.报价编号 and 随车类型 = '厂家' and 随车名称 = 'SSSC'), 0) as '厂家SI_SSSC实收',  \n" +
//                "  ifnull((select cast(sum(厂家返利) as decimal) from 报价随车表 where 报价编号 = 报价单表.报价编号 and 随车类型 = '厂家' and 随车名称 = '旅游礼包'), 0) as '厂家SI_旅游礼包实收',  \n" +
//                "  ifnull((select cast(sum(厂家返利) as decimal) from 报价随车表 where 报价编号 = 报价单表.报价编号 and 随车类型 = '厂家' and 随车名称 = 'SD卡'), 0) as '厂家SI_SD卡实收',  \n" +
//                "  ifnull((select cast(sum(厂家返利) as decimal) from 报价随车表 where 报价编号 = 报价单表.报价编号 and 随车类型 = '厂家' and 随车名称 = '加油卡'), 0) as '厂家SI_加油卡实收',  \n" +
//                "  ifnull((select cast(sum(厂家返利) as decimal) from 报价随车表 where 报价编号 = 报价单表.报价编号 and 随车类型 = '厂家' and 随车名称 = '实物礼品'), 0) as '厂家SI_实物礼品实收',  \n" +
//                "  ifnull((select cast(sum(厂家返利) as decimal) from 报价随车表 where 报价编号 = 报价单表.报价编号 and 随车类型 = '厂家' and 随车名称 = '服务礼券'), 0) as '厂家SI_服务礼券实收',  \n" +

            "  ifnull((select ifnull(贴息金额, 0) from lsh金融申请附属 where 初始报价单号 = 报价单表.初始报价单号), 0) as '贴息金额',  \n" +

            "  ifnull((SELECT IFNULL(a.BMBS_retail_SI, 0.00)  \n" +
            "    FROM snp_setting a WHERE a.carCode in (select 车型编号 from lsh车型 where (来源, 品牌, 级别, 车型, 车型详细) in (  \n" +
            "	select 来源, 品牌, 级别, 车型, 车型详细 from lsh车型 where 车型编号 = (select 车型编号 from lsh库存表 where 库存编号 = 库存表.库存编号)))   \n" +
            "    and a.permit = 库存表.所属经销商 and a.y_m = '190001'   \n" +
            "    AND (a.apperanceCode in (select 颜色代码 FROM lsh库存表 where 库存编号 = 库存表.库存编号) or a.apperanceCode = 0) ORDER BY a.apperanceCode DESC LIMIT 1), 0) as '厂家零售SI',  \n" +

            "  ifnull((SELECT IFNULL(a.BMBS_wholesale_SI, 0.00)  \n" +
            "    FROM snp_setting a WHERE a.carCode in (select 车型编号 from lsh车型 where (来源, 品牌, 级别, 车型, 车型详细) in (  \n" +
            "	select 来源, 品牌, 级别, 车型, 车型详细 from lsh车型 where 车型编号 = (select 车型编号 from lsh库存表 where 库存编号 = 库存表.库存编号)))   \n" +
            "    and a.permit = 库存表.所属经销商 and a.y_m = '190001'   \n" +
            "    AND (a.apperanceCode in (select 颜色代码 FROM lsh库存表 where 库存编号 = 库存表.库存编号) or a.apperanceCode = 0) ORDER BY a.apperanceCode DESC LIMIT 1), 0) as '厂家批售SI',  \n" +

            "  ifnull((select cast(费用折后价 as decimal(17, 2)) from 报价费用详细表 where 费用编号 = 报价单表.费用编号 and 费用名称 = '上牌服务费'), 0) as '上牌服务费',  \n" +
            "  ifnull((select cast(厂家延保MSRP as decimal(17, 2))   from 报价五星延保 where 延保编号 = 报价单表.延保编号), 0) as '厂家延保MSRP(含税)',  \n" +
            "  ifnull((select cast(延保原价 as decimal(17, 2))       from 报价五星延保 where 延保编号 = 报价单表.延保编号), 0) as '本店延保MSRP(含税)',  \n" +
            "  ifnull((select cast(第三方延保MSRP as decimal(17, 2))  from 报价五星延保 where 延保编号 = 报价单表.延保编号), 0) as '第三方延保MSRP(含税)',  \n" +
            "  ifnull((select cast(厂家延保实收 as decimal(17, 2))    from 报价五星延保 where 延保编号 = 报价单表.延保编号), 0) as '厂家延保实收金额(含税)',  \n" +
            "  ifnull((select cast(延保折后价 as decimal(17, 2))      from 报价五星延保 where 延保编号 = 报价单表.延保编号), 0) as '本店延保实收金额(含税)',  \n" +
            "  ifnull((select cast(第三方延保实收 as decimal(17, 2))   from 报价五星延保 where 延保编号 = 报价单表.延保编号), 0) as '第三方延保实收金额(含税)',  \n" +
            "  ifnull((select cast(厂家延保MSRP as decimal(17, 2))    from 报价五星延保 where 延保编号 = 报价单表.延保编号), 0) as '厂家延保成本(含税)',  \n" +
            "  ifnull((select cast(延保原价 as decimal(17, 2))        from 报价五星延保 where 延保编号 = 报价单表.延保编号), 0) as '本店延保成本(含税)',  \n" +
            "  ifnull((select cast(第三方延保MSRP as decimal(17, 2))  from 报价五星延保 where 延保编号 = 报价单表.延保编号), 0) as '第三方延保成本(含税)',  \n" +
            "  ifnull((select cast(ifnull(厂家延保MSRP, 0) as decimal(17, 2))    from 报价五星延保 where 延保编号 = 报价单表.延保编号 and 厂家延保实收 = 0), 0) as '随车厂家延保成本',  \n" +
            "  ifnull((select cast(ifnull(第三方延保msrp, 0) as decimal(17, 2))    from 报价五星延保 where 延保编号 = 报价单表.延保编号 and 第三方延保实收 = 0), 0) as '随车第三方延保成本',  \n" +
            "  ifnull((select cast(ifnull(厂家延保实收, 0) - ifnull(厂家延保MSRP, 0) as decimal(17, 2))    from 报价五星延保 where 延保编号 = 报价单表.延保编号 and 厂家延保实收 <> 0), 0) / 1.17 as '现金销售厂家延保延保毛利',  \n" +
            "  ifnull((select cast(ifnull(延保折后价, 0) - ifnull(延保原价, 0) as decimal(17, 2))          from 报价五星延保 where 延保编号 = 报价单表.延保编号 and 延保折后价 <> 0), 0) / 1.17 as '现金销售本店延保延保毛利',  \n" +
            "  ifnull((select cast(ifnull(第三方延保实收, 0) - ifnull(第三方延保MSRP, 0) as decimal(17, 2)) from 报价五星延保 where 延保编号 = 报价单表.延保编号 and 第三方延保实收 <> 0), 0) / 1.17 as '现金销售本店第三方延保毛利',  \n" +
            "  ifnull((select sum(ifnull(折后价, 0) * ifnull(数量, 0))                     from 报价精品详细表 where 编号 = 报价单表.精品 and 折后价 > 0), 0) as '精品销售总价',  \n" +
            "  round(ifnull((select sum(((ifnull(折后价, 0) - ifnull(成本, 0))) * ifnull(数量, 0)) from 报价精品详细表 where 编号 = 报价单表.精品 and 折后价 > 0), 0)/1.17,2) as '现金销售精品产品毛利',  \n" +
            "  if((((select 状态 from lsh金融申请 where 初始报价单号 = 报价单表.初始报价单号) = '03' or (select 状态 from lsh金融申请 where 初始报价单号 = 报价单表.初始报价单号) = '04') and 报价单表.金融 is not null) or 报价单表.金融 is null, '否', '是') as '是否带金融',  \n" +
            "  ifnull((select cast(金融手续费 as decimal(17, 2)) from 报价金融表 where 金融编号 = 报价单表.金融), 0) as '金融手续费',  \n" +
            "  ifnull((select cast(金融服务费 as decimal(17, 2)) from 报价金融表 where 金融编号 = 报价单表.金融), 0) as '金融服务费',  \n" +
            "  if((((select 状态 from lsh保险申请附属 where 初始报价单号 = 报价单表.初始报价单号) ='04' or (select 状态 from lsh保险申请附属 where 初始报价单号 = 报价单表.初始报价单号) ='03') and 报价单表.保险编号 is not null) or 报价单表.保险编号 is null, '否', '是') as '是否带保险',  \n" +
            "  ifnull((select cast(sum(手续费) as decimal(17, 2)) from 报价保险详细表 where 保险编号 = 报价单表.保险编号 ), 0) as '新保手续费',  \n" +   // 姜老师确认，新保手续费不应该去除“盗抢险”
            "  ifnull((select cast(ifnull(手续费, 0) as decimal(17, 2)) from 报价保险详细表 where 保险编号 = 报价单表.保险编号 and 险种 = '全车盗抢险' and 第N年 = 1), 0) as '盗抢险手续费',  \n" +
            "  ifnull((select ifnull(置换手续费, 0) from 报价置换表 where 置换编号 = 报价单表.置换编号), 0) as '置换手续费',  \n" +
            "  ifnull((select cast(费用原价 as decimal(17, 2)) from 报价费用详细表 where 费用编号 = 报价单表.费用编号 and 费用名称 = '上牌服务费成本'), 0) as '上牌服务费成本',  \n" +
            "  ifnull((select ifnull(其他一, 0) - ifnull(其他一成本, 0) from 报价预收表 where 预收编号 = 报价单表.预收编号), 0) / 1.17 as '其他一毛利',  \n" +
            "  ifnull((select ifnull(其他二, 0) - ifnull(其他二成本, 0) from 报价预收表 where 预收编号 = 报价单表.预收编号), 0) / 1.17 as '其他二毛利',  \n" +
            "  ifnull((select cast(费用折后价 as decimal(17, 2)) from 报价费用详细表 where 费用编号 = 报价单表.费用编号 and 费用名称 = '其他服务费'), 0) as '销售服务费',  \n" +
            "  ifnull((select cast(费用折后价 as decimal(17, 2)) from 报价费用详细表 where 费用编号 = 报价单表.费用编号 and 费用名称 = '其他服务费'), 0) as '其他服务费',  \n" +
            "  ifnull((select ifnull(其他三, 0) from 报价预收表 where 预收编号 = 报价单表.预收编号), 0) / 1.17 as '其他三毛利',  \n" +
            "  ifnull((select ifnull(其他四, 0) from 报价预收表 where 预收编号 = 报价单表.预收编号), 0) / 1.17 as '其他四毛利',  \n" +
            "  ifnull((select ifnull(其他五, 0) from 报价预收表 where 预收编号 = 报价单表.预收编号), 0) / 1.17 as '其他五毛利',  \n" +
            "  ' ' as '配车天数',  \n" +
            "  ' ' as '配车日期',  \n" +
            "  ' ' as '备注',  \n" +
            "  if((select status from custom_car_configure where store_id = 库存表.库存编号) = 1, '是', '否') as '是否预装'  \n";

    // 为了计算订单总价，需要关联的表
    private static final String endOrderSql = ") b \n" +
            "join 报价单表 a on b.报价编号 = a.报价编号\n" +
            "LEFT JOIN (  \n" +
            "SELECT SUM( IF ( `险种` = '交强险', `折后价`, 0 )) + SUM( IF ( `险种` = '交强险', `不计免赔`, 0 )) AS 交强险,  \n" +
            "\t SUM( IF ( `险种` = '车船税', `折后价`, 0 )) + SUM( IF ( `险种` = '车船税', `不计免赔`, 0 )) AS 车船税,  \n" +
            "\t SUM( IF ( `险种` <> '交强险'&&`险种`<>'车船税', `折后价`, 0 )) + SUM( IF ( `险种` <> '交强险'&&`险种`<>'车船税', `不计免赔`, 0 )) AS 商业险,  \n" +
            "\t SUM( IF ( `险种` != '车船税', `折后价`, 0 )) + SUM( IF ( `险种` != '车船税', `不计免赔`, 0 )) AS 保险总价,保险编号 FROM `报价保险详细表` group by 保险编号 \n" +
            ") c ON a.保险编号 = c.保险编号 \n" +
            "LEFT JOIN (select MAX(case when 费用名称 = '上牌服务费' then 费用折后价 end) 上牌服务费 ,      \n" +
            "\t\t\t\t MAX(case when 费用名称 = '其他服务费' then 费用折后价 end) 其他服务费,      \n" +
            "\t\t\t\t 费用编号      \n" +
            "\t\t\t\t from 报价费用详细表 GROUP BY 费用编号      \n" +
            "\t\t ) d ON a.费用编号 = d.费用编号      \n" +
            "LEFT JOIN 报价精品表 e ON  a.精品 = e.编号      \n" +
            "LEFT JOIN 报价五星延保 f ON f.延保编号 = a.延保编号      \n" +
            "LEFT JOIN 报价金融表 g ON g.金融编号 = a.金融    \n" +
            "LEFT JOIN   报价置换表 h ON a.置换编号 = h.置换编号    \n" +
            "LEFT JOIN 报价预收表 i ON a.预收编号 = i.预收编号 ";

    private static Logger logger = LoggerFactory.getLogger(SalesRepositoryImpl.class);

    @Override
    public PageData getSaleList(String permit, SalesService.GetSaleListRequest request) {
        String sql = "select * from LSH经销商表\n" + "where 1 = 1\n";
        // permit
        Map params = Maps.newHashMap();
        params.put("permit", permit);
        if (!Strings.isNullOrEmpty(request.area1)) {
            sql += "and 大区 = :area1 \n";
            params.put("area1", request.area1);
        }
        if (!Strings.isNullOrEmpty(request.area2)) {
            sql += "and 小区 = :area2 \n";
            params.put("area2", request.area2);
        }
        if (!Strings.isNullOrEmpty(request.province)) {
            sql += "and 省份 = :province\n";
            params.put("province", request.province);
        }
        if (!Strings.isNullOrEmpty(request.city)) {
            sql += "and 城市 = :city\n";
            params.put("city", request.city);
        }
        if (!Strings.isNullOrEmpty(request.sale)) {
            sql += "and 经销商名 like :sale\n";
            params.put("sale", "%" + request.sale + "%");
        }
        if (!Strings.isNullOrEmpty(request.licence)) {
            sql += "and 许可_LSH = :licence\n";
            params.put("licence", request.licence);
        }
        sql += "order by 更新时间 desc";
        return this.getPage(sql, params, request.getPageSize(), request.getPageIndex());
    }

    @Override
    public PageData getCarItemList(String permit, RequestBase request) {
        String sql = "select * " + "from LSH随车项目设置 " + "where 许可 = :permit";
        Map params = Maps.newHashMap();
        params.put("permit", permit);
        return this.getPage(sql, params, request.getPageSize(), request.getPageIndex());
    }

    @Override
    public PageData getAttachmentPolicyList(String permit, SalesService.GetAttachmentPolicyListRequest request) {
        String sql = "select * from attachmentPolicy \n";
        sql += "where 1 = 1 \n";
        sql += "and permit = 'LSH'\n"; // add by cy,添加许可判断
        Map params = Maps.newHashMap();
        params.put("permit", permit);
        if (!Strings.isNullOrEmpty(request.siCode)) {
            sql += "and si_program_code_english = :siCode \n";
            params.put("siCode", request.siCode);
        }

        if (!Strings.isNullOrEmpty(request.siName)) {
            sql += "and si_program_name_chinese like :siName \n";
            params.put("siName", "%" + request.siName + "%");
        }

        if (request.releaseDate != null) {
            sql += "and BMBS_release_date = :releaseDate \n";
            params.put("releaseDate", DateFormat.getDateInstance().format(request.releaseDate));
        }

        if (request.financeProductNumber != 0) {
            sql += "and financeProductNumber = :financeProductNumber \n";
            params.put("financeProductNumber", request.financeProductNumber);
        }

        if (request.startDate != null) {
            sql += "and startDate >= :startDate \n";
            params.put("startDate", DateFormat.getDateInstance().format(request.startDate));
        }
        if (request.endDate != null) {
            sql += "and endDate <= :endDate \n";
            params.put("endDate", DateFormat.getDateInstance().format(request.endDate));
        }
        if (!Strings.isNullOrEmpty(request.siStatus)) {
            sql += "and SI_status = :siStatus \n";
            params.put("siStatus", request.siStatus);
        }
        return this.getPage(sql, params, request.getPageSize(), request.getPageIndex());
    }

    // add attachmentPolicyList search
    @Override
    public PageData getAttachmentPolicyListByCondition(String permit, SalesService.GetAttachmentPolicyListReq request) {
        String sql = "select * from attachmentPolicy A, LSH车型 B\n";
        sql += "where 1 = 1 \n";
        sql += "and A.permit = 'LSH'\n"; // 添加许可判断
        sql += "and A.car = B.车型编号\n"; // 添加外键
        Map params = Maps.newHashMap();
        params.put("permit", permit);
        if (!Strings.isNullOrEmpty(request.siCode)) {
            sql += "and A.si_program_code_english = :siCode \n";
            params.put("siCode", request.siCode);
        }

        if (!Strings.isNullOrEmpty(request.siName)) {
            sql += "and A.si_program_name_chinese like :siName \n";
            params.put("siName", "%" + request.siName + "%");
        }

        if (!Strings.isNullOrEmpty(request.siStatus)) {
            sql += "and A.SI_status = :siStatus \n";
            params.put("siStatus", request.siStatus);
        }

        if (!Strings.isNullOrEmpty(request.siType)) {
            sql += "and A.itemType like :siType \n";
            params.put("siType", "%" + request.siType + "%");
        }

        if (!Strings.isNullOrEmpty(request.carInfo)) {
            sql += "and ( B.品牌 like :carInfo or B.车型 like :carInfo or B.车型详细 like :carInfo )\n";
            params.put("carInfo", "%" + request.carInfo + "%");
        }

        if (request.siFinance1 != 0 && request.siFinance2 != 0) {
            sql += "and A.item >= :siFinance1 and A.item <= :siFinance2 \n";
            params.put("siFinance1", request.siFinance1);
            params.put("siFinance2", request.siFinance2);
        }
        return this.getPage(sql, params, request.getPageSize(), request.getPageIndex());
    }

    @Override
    public PageData getActiveCarItemSettingList(String permit, SalesService.GetCarItemSettingRequest request) {
        String sql = "select * from LSH随车项目设置 \n";
        sql += "where 1 = 1 \n";
        Map params = Maps.newHashMap();
        params.put("permit", permit);

        if (request.car != null && request.car > 0) {
            sql += "and 车型编号 = :car \n";
            params.put("car", request.car);
        }

        if (!Strings.isNullOrEmpty(request.permit)) {
            sql += "and 许可 = :permit \n";
            params.put("permit", request.permit);
        }

        sql += "and :dateNow >= 启用时间 \n";
        sql += "and ((:dateNow <= 停用时间 and 停用时间 is not null) or (停用时间 is null)) \n";
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        Date dateNow = null;
        try {
            dateNow = sdf.parse(sdf.format(new Date()));
        } catch (Exception e) {
            logger.error("运行异常。", e);
        }
        params.put("dateNow", dateNow);
        sql += "order by 启用时间 desc limit 1 \n";

        sql = "select * from (" + sql + ") a";

        return this.getPage(sql, params, request.getPageSize(), request.getPageIndex());
    }

    @Override
    public PageData getStoreInfoes(String dealer, Map map, RequestBase request) {
        long startTime = System.currentTimeMillis();

        try {
            String sql = " SELECT * FROM (SELECT 报价单表.`初始报价单号`,  报价单表.`报价编号`, `LSH库存表a`.`生产日期`,  `LSH库存表a`.`库存编号`,   \n" +
                    " `LSH库存表a`.`车款年份`, `LSH库存表a`.car_state,    `报价交车表`.`交车日期` ,      \n" +
                    " IF (`LSH库存表a`.`生产号` LIKE '%IRISTechNV_%' OR `LSH库存表a`.`生产号` LIKE '%IRISTechnologies%','', `LSH库存表a`.`生产号`)`生产号`,   \n" +
                    " IF (`LSH库存表a`.`车架号` LIKE '%IRISTechNV_%' OR `LSH库存表a`.`车架号` LIKE '%IRISTechnologies%','',`LSH库存表a`.`车架号`) `车架号（VIN）`,  \n" +
                    " `LSH库存表a`.`欧版底盘号` `欧版底盘号（FIN)`, `LSH库存表a`.`发动机号`, `LSH库存表a`.`VSB号`,    \n" +
                    " `LSH库存表a`.`颜色代码`, `LSH库存表a`.`颜色`, `LSH库存表a`.`内饰代码`, `LSH库存表a`.`内饰`, `LSH库存表a`.`原厂配置`, `LSH库存表a`.`加配配置`,    \n" +
                    " `LSH库存表a`.`加配价格含税` `加配价格（含税）`, IFNULL(`LSH库存表a`.`采购价格含税`,0) `采购价格（含税）`, IFNULL(`LSH库存表a`.`采购价格未税`,0) `采购价格（不含税）`, `LSH库存表a`.`标配MSRP含税` `标配MSRP（含税）`,    \n" +
                    " `LSH库存表a`.`实际MSRP含税` `实际MSRP（含税）`,  `LSH库存表a`.`厂家确认订单日期`, `LSH库存表a`.`预计到港日期` `预计到港日期（ETA）`,    \n" +
                    " `LSH库存表a`.`实际到港日期` `实际到港日期（ATA）`, `LSH库存表a`.`预计下线日期`, `LSH库存表a`.`港口代码`, `LSH库存表a`.`港口名称`, `LSH库存表a`.`车辆物流状态`,    \n" +
                    " `LSH库存表a`.`车辆零售状态`, `报价订单表`.`销售类型` 车辆零售类型, `LSH库存表a`.`HOLD日期`, `LSH库存表a`.`申请释放日期`, `LSH库存表a`.`批发日期` 采购日期, `LSH库存表a`.`批发付款到期日`,    \n" +
                    " `LSH库存表a`.`NBI车辆状态`, `LSH库存表a`.`预计到店日期`, `LSH库存表a`.`店内车辆位置`, `LSH库存表a`.`二次PDI完成日期`, `LSH库存表a`.`二次PDI结果`,    \n" +
                    " `LSH库存表a`.`入库日期`, `LSH库存表a`.`出库日期`, `LSH库存表a`.`库龄天数` 入库天数, `LSH库存表a`.`建议三次PDI日期`, `LSH库存表a`.`三次PDI完成日期` `三次PDI 完成日期`,    \n" +
                    " `LSH库存表a`.`三次PDI结果` `三次PDI 结果`, `LSH库存表a`.`车辆受损情况`, `LSH库存表a`.`商检单到店状态`, `LSH库存表a`.`关单到店状态`, `LSH库存表a`.`合格证到店状态`,    \n" +
                    " `LSH库存表a`.`合格证号`, `LSH库存表a`.`商检单号` AS '商检单到店日期', `LSH库存表a`.`关单号` AS '关单到店日期', `LSH库存表a`.`批发商`,  `LSH库存表a`.`外部可见级别`,    \n" +
                    "   \n" +
                    " `LSH车型`.`MSRP` `BI车款标配MSRP（含税）`,`LSH车型`.`品牌` `品牌`,`LSH车型`.`来源`,`LSH车型`.`级别` 车系, `LSH车型`.`车型`, `LSH车型`.`车型详细` 车款,   \n" +
                    " `报价单表`.`订单编号`, `报价单表`.`金融`, `报价单表`.`折后价`,`报价单表`.`原价`,     \n" +
                    " IFNULL(`报价单表`.`车辆现金折扣`, 0) `车辆现金折扣`, ROUND(IFNULL(`报价单表`.`车辆销售价`,`报价单表`.`折后价`),2) `车辆折后价`, ROUND(`报价单表`.`折后价`,2) `车辆销售价`, ROUND((`报价单表`.`折后价` -  IFNULL(`报价单表`.`消费税`,0) )/1.17,2) `不含税价`, `报价单表`.`消费税` `消费税`, `报价单表`.`消费税` `消费税及附加`,   \n" +
                    "   \n" +
                    " IFNULL(`报价金融表`.`首付额`,0) `首付额`, IFNULL(`报价金融表`.`金融服务费`,0) as `金融服务费`, IFNULL(`报价金融表`.`金融手续费`,0) as `金融手续费`, IFNULL(`报价金融表`.`保证金金额`,0) `保证金金额`,   \n" +
                    "   \n" +
                    " `报价交车表`.`合同签约日期` `合同签署日期`,`报价交车表`.`开票时间` `开票日期`, `报价交车表`.`退票时间`, `报价交车表`.`合同金额`,    \n" +
                    " `报价交车表`.`余款金额`,`报价交车表`.`上牌省` `上牌省份`,`报价交车表`.`上牌市` `上牌城市`,`报价交车表`.`上牌区` `上牌行政区`,`报价交车表`.`上牌地址` `上牌具体地址`,    \n" +
                    " `报价交车表`.`上牌日期`,`报价交车表`.`牌照号`,`报价交车表`.`开票客户姓名` `车主/开票人名称`,`报价交车表`.`开票客户机构代码` `车主/开票人组织机构代码`,    \n" +
                    " IF (`报价交车表`.`开票客户身份证类型` = '身份证',`报价交车表`.`开票客户身份证`,'') `车主/开票人身份证号码`,    \n" +
                    " IF (`报价交车表`.`开票客户身份证类型` = '外籍ID', `报价交车表`.`开票客户身份证`, '' ) `外籍车主车主/开票人ID号码`,    \n" +
                    " IF (`报价交车表`.`交车日期` IS NULL, '', `报价交车表`.`售后联系人`) `售后联系人1`, IF (`报价交车表`.`交车日期` IS NULL,'',`报价交车表`.`售后联系人关系`) `售后联系人1身份`,   \n" +
                    " IF (`报价交车表`.`交车日期` IS NULL, '', `报价交车表`.`售后联系人电话`) `售后联系人1电话号码`, IF (`报价交车表`.`交车日期` IS NULL, '', `报价交车表`.`售后联系人地址`) `售后联系人1联系地址`,  \n" +
                    " IF (`报价交车表`.`交车日期` IS NULL, '', `报价交车表`.`售后联系人微信` ) `售后联系人1微信ID`,  \n" +
                    " `报价交车表`.`开票客户电话` `车主/开票人电话号码`,`报价交车表`.`提车代理人`,`报价交车表`.`提车代理人身份证`,' ' `外籍提车代理人ID号码`,  \n" +
                    " `报价交车表`.`售后联系人电话`,`报价交车表`.`售后联系人地址`,`报价交车表`.`售后联系人微信`,`报价交车表`.`开票客户身份证`,     \n" +
                    "   \n" +
                    " `报价客户表`.`上牌类型`, `报价客户表`.`邮箱` `顾客email`,     \n" +
                    " IF(`报价客户表`.`身份证类型` = '身份证',`报价客户表`.`身份证`,'') `顾客身份证号码`,    \n" +
                    " IF (`报价客户表`.`身份证类型` = '外籍ID',`报价客户表`.`身份证`,'') `顾客外籍ID号码`,    \n" +
                    " IF (`报价客户表`.`身份证类型` = '组织结构',`报价客户表`.`身份证`,'') `组织机构代码`,    \n" +
                    "   \n" +
                    " `报价订单表`.`订单流水号` `Case号`, `报价订单表`.`订单号`,   顾客记录汇总.顾客姓名 `订单客户`,    \n" +
                    " IF (`报价订单表`.`订金类型` = '意向金',`报价订单表`.`订金金额`,'') `意向金金额`, IF (`报价订单表`.`订金类型` = '订金',`报价订单表`.`订金金额`,'') `订金金额`, `报价订单表`.`订金金额` `订金金额_ORI`,   \n" +
                    " `报价订单表`.`无息退款天数` `意向金无息退款天数`,`报价订单表`.`资源释放期限` `释放资源期限`,`报价订单表`.`申请修改原因`,`报价订单表`.`撤销订单原因`,    \n" +
                    " `报价订单表`.`订单审批通过日期`,`报价订单表`.`订单签署日期`,`报价订单表`.`订金交付日期`,`报价订单表`.`订单失销日期`,`报价订单表`.`退订订金金额` `退订金金额`,   \n" +
                    " `报价订单表`.`退订金转账日期`,`订单修改日期`, `报价订单表`.`订金加减金额` `订金+/-金额`,`报价订单表`.`订金加减日期` `订金+/-日期`,`报价订单表`.`预计开票日期` `合同交货日期`,    \n" +
                    "   \n" +
                    " `报价置换表`.`首次上牌日期` `置换车辆上牌年月`, IFNULL(`报价置换表`.`置换估价`,0) `置换估价`, `报价置换表`.`置换估价` `二手车价格`,   \n" +
                    " `报价置换表`.`置换类型` `店内置换/店外置换`,`报价置换表`.`置换评估费`,     \n" +
                    " `报价置换表`.`置换手续费`,  \n" +
                    "   IF (`报价置换表`.`置换估价` > 0, '是', '') `是否置换`, \n" +
                    " IFNULL(`报价五星延保`.`延保折后价`,0) `延保折后价`,  \n" +
                  //  " if(IFNULL(`报价五星延保`.`延保折后价`,0) > 0, (IFNULL(`报价五星延保`.`延保折后价`,0) - IFNULL(`报价五星延保`.`延保原价`,0)) / 1.17, 0) `现金销售五星延保毛利`,  \n" +
                    " IFNULL(`报价五星延保`.`延保原价`,0) `延保原价`,  \n" +

                    " ifnull(`报价五星延保`.`延保原价`, 0.0) as `本店延保MSRP(含税)`,    \n" +
                    " ifnull(`报价五星延保`.`厂家延保MSRP`, 0.0) as `厂家延保MSRP(含税)`,    \n" +
                    " ifnull(`报价五星延保`.`第三方延保MSRP`, 0.0) as `第三方延保MSRP(含税)`,    \n" +
                    " ifnull(`报价五星延保`.`延保折后价`, 0.0) as `本店延保实收金额(含税)`,  \n" +
                    " ifnull(`报价五星延保`.`厂家延保实收`, 0.0) as `厂家延保实收金额(含税)`,  \n" +
                    " ifnull(`报价五星延保`.`第三方延保实收`, 0.0) as `第三方延保实收金额(含税)`,  \n" +
                    " ifnull(`报价五星延保`.`延保原价`, 0.0) as `本店延保成本(含税)`,  \n" +
                    " ifnull(`报价五星延保`.`厂家延保MSRP`, 0.0) as `厂家延保成本(含税)`,  \n" +
                    " ifnull(`报价五星延保`.`第三方延保MSRP`, 0.0) as `第三方延保成本(含税)`,  \n" +

                    " ifnull((select sum(ifnull(报价五星延保.厂家延保msrp, 0))   \n" +
                    " FROM 报价五星延保   \n" +
                    " WHERE 报价五星延保.延保编号 = 报价单表.延保编号 and 报价五星延保.厂家延保实收 = 0 ), 0)  \n" +
                    "    + ifnull((select sum(ifnull(报价五星延保.第三方延保msrp, 0)) from 报价五星延保 where 报价五星延保.延保编号 = 报价单表.延保编号 and 报价五星延保.第三方延保实收 = 0), 0) as 延保赠送成本,    \n" +

                    " SUM( IFNULL(`报价随车表`.厂家返利,0)) 厂家返利,   \n" +
                    " SUM( IF(`报价随车表`.随车类型 = '经销商', `报价随车表`.随车成本, 0) )`经销商随车成本`,   \n" +
                    " SUM( IF(`报价随车表`.随车类型 = '厂家', `报价随车表`.随车成本, 0) )`厂商随车成本`,   \n" +
                    " SUM( IF(`报价随车表`.随车类型 = '厂家' AND `报价随车表`.随车名称  <> '购置税', `报价随车表`.随车成本, 0) )`厂商非购置税随车成本`,   \n" +
                    " SUM( IF(`报价随车表`.随车类型 = '经销商' AND `报价随车表`.随车部门='销售部',`报价随车表`.随车成本, 0) )`经销商赠送成本`,   \n" +
                    " SUM( IF(`报价随车表`.随车类型 = '经销商' AND `报价随车表`.随车部门='销售部',`报价随车表`.随车成本, 0) )`经销商赠送成本不含延保精品`,   \n" +
                    " SUM( IF(`报价随车表`.随车类型 <> '购置税' AND `报价随车表`.随车部门='销售部', `报价随车表`.随车成本, 0)) `厂家随车SI成本`,   \n" +
                    " SUM( IF(`报价随车表`.随车类型 = '厂家' AND `报价随车表`.随车名称 = '保险', `报价随车表`.随车成本, 0) )`厂家SI_保险成本`,   \n" +
                    " SUM(  IF(`报价随车表`.随车类型 = '厂家' AND `报价随车表`.随车名称 = '保险', `报价随车表`.厂家返利, 0) )`厂家SI_保险实收`,   \n" +
                    " SUM(  IF(`报价随车表`.随车类型 = '厂家' AND `报价随车表`.随车名称 = '购置税', `报价随车表`.随车成本, 0) )`厂家SI_购置税成本`,   \n" +
                    " SUM(  IF(`报价随车表`.随车类型 = '厂家' AND`报价随车表`. 随车名称 = '购置税', `报价随车表`.厂家返利, 0)) `厂家SI_购置税实收`,   \n" +
                    " SUM(  IF(`报价随车表`.随车类型 = '厂家' AND `报价随车表`.随车名称 = 'SSSC', `报价随车表`.随车成本, 0)) `厂家SI_SSSC成本`,   \n" +
                    " SUM(  IF(`报价随车表`.随车类型 = '厂家' AND `报价随车表`.随车名称 = 'SSSC', `报价随车表`.厂家返利, 0) )`厂家SI_SSSC实收`,   \n" +
                    " SUM(  IF(`报价随车表`.随车类型 = '厂家' AND `报价随车表`.随车名称 = '旅游礼包', `报价随车表`.随车成本, 0) )`厂家SI_旅游礼包成本`,   \n" +
                    " SUM(  IF(`报价随车表`.随车类型 = '厂家' AND `报价随车表`.随车名称 = '旅游礼包', `报价随车表`.厂家返利, 0) )`厂家SI_旅游礼包实收`,   \n" +
                    " SUM(  IF(`报价随车表`.随车类型 = '厂家' AND `报价随车表`.随车名称 = 'SD卡', `报价随车表`.随车成本, 0)) `厂家SI_SD卡成本`,   \n" +
                    " SUM(  IF(`报价随车表`.随车类型 = '厂家' AND `报价随车表`.随车名称 = 'SD卡', `报价随车表`.厂家返利, 0) )`厂家SI_SD卡实收`,   \n" +
                    " SUM(  IF(`报价随车表`.随车类型 = '厂家' AND `报价随车表`.随车名称 = '加油卡', `报价随车表`.随车成本, 0))`厂家SI_加油卡成本`,   \n" +
                    " SUM(  IF(`报价随车表`.随车类型 = '厂家' AND `报价随车表`.随车名称 = '加油卡', `报价随车表`.厂家返利, 0)) `厂家SI_加油卡实收`,   \n" +
                    " SUM(  IF(`报价随车表`.随车类型 = '厂家' AND `报价随车表`.随车名称 = '实物礼品', `报价随车表`.随车成本, 0) )`厂家SI_实物礼品成本`,   \n" +
                    " SUM(  IF(`报价随车表`.随车类型 = '厂家' AND `报价随车表`.随车名称 = '实物礼品', `报价随车表`.厂家返利, 0) )`厂家SI_实物礼品实收`,   \n" +
                    " SUM(  IF(`报价随车表`.随车类型 = '厂家' AND `报价随车表`.随车名称 = '服务礼券', `报价随车表`.随车成本, 0)) `厂家SI_服务礼券成本`,   \n" +
                    " SUM(  IF(`报价随车表`.随车类型 = '厂家' AND `报价随车表`.随车名称 = '服务礼券', `报价随车表`.厂家返利, 0) )`厂家SI_服务礼券实收`,   \n" +
                    " SUM(  IF(`报价随车表`.随车类型 = '经销商' AND `报价随车表`.随车名称 = '保险', `报价随车表`.随车成本, 0) )`经销商_保险成本`,   \n" +
                    " SUM(  IF(`报价随车表`.随车类型 = '经销商' AND `报价随车表`.随车名称 = '贴息', `报价随车表`.随车成本, 0)) `经销商SI_贴息成本`,   \n" +
                    " SUM(  IF(`报价随车表`.随车类型 = '厂家' AND `报价随车表`.随车名称 = '贴息', `报价随车表`.随车成本, 0) )`厂家SI_贴息成本`,    \n" +
                    " ROUND(`报价付款详细`.`应付金额`,2) `应收金额`, ROUND(`报价付款详细`.`实付金额`,2) `实收金额`,   \n" +
                    " `库存利润表`.`上报厂家`,`库存利润表`.`上报厂家日期`,`库存利润表`.`上报SIMS`,`库存利润表`.`上报SIMS日期`,`库存利润表`.`后市场产品毛利` `后市场毛利`,   \n" +
                    " `所属经销商`.`经销商名` `所属经销商`, IF(`采购批发商`.`经销商名` IS NULL,LSH库存表a.采购批发商,`采购批发商`.`经销商名`) `采购经销商`, `LSH库存内部可见`.`内部可见角色` `内部可见级别`, `用户`.`人员姓名`  `销售顾问`,   \n" +
                    " ' ' `代付款`,  income_adjustment.si_adjust,income_adjustment.finance_poundage_adjust, income_adjustment.insurance_poundage_adjust, income_adjustment.after_market_product_cost_adjust, income_adjustment.five_stars_extend_cost_adjust, income_adjustment.finance_soft_loan_adjust,   \n" +

                    " (select CONCAT(`交强险手续费`, '|', `商业险手续费`,'|', IFNULL(SUM(折后价),0), '|', `交强险手续费` + `商业险手续费`)    \n" +
                    " from `报价保险表` `报价保险表`,`报价保险详细表` `报价保险详细表`   \n" +
                    " where `报价单表`.`保险编号` = `报价保险表`.`保险编号` AND `报价保险表`.`保险编号` = `报价保险详细表`.`保险编号` and `险种` <> '车船税'   \n" +
                    " group by `报价保险表`.`保险编号`) `报价保险` ,   \n" +
                    " (select ifnull(手续费, 0) from 报价保险详细表 where 保险编号 = `报价单表`.`保险编号` and 险种 = '全车盗抢险' and 第N年 = 1) as '盗抢险手续费',  \n" +

                    " (SELECT  IFNULL(SUM(poundage),0)  AS poundage\n" +
                    "  FROM lsh保险申请表附属_详细 \n" +
                    "  WHERE `报价单表`.初始报价单号= lsh保险申请表附属_详细.初始报价单号  ) AS 保险批单费,"+
                    " (select CONCAT(IFNULL(经销商采购价未税,'0'),'|',IFNULL(`精品折后价`,0), '|', IFNULL(SUM(ROUND(折后价,2) * 数量),0), '|', IFNULL(SUM(成本 * 数量),0))    \n" +
                    " from `报价精品表` `报价精品表`, `报价精品详细表` `报价精品详细表`, `LSH精品套餐详细` `LSH精品套餐详细`, `LSH精品信息` `LSH精品信息`      \n" +
                    " where `报价单表`.`精品` = 报价精品表.`编号` and `报价精品表`.`编号` = `报价精品详细表`.`编号`  and 报价精品详细表.折后价 > 0 and `报价精品详细表`.ID = `LSH精品套餐详细`.`套餐编号`   \n" +
                    "    and  `LSH精品信息`.精品编号 = `LSH精品套餐详细`.精品编号  group by 报价精品表.`编号`) `精品信息` ,   \n" +

                    " (select IFNULL(SUM(ROUND(折后价,2) * 数量) ,0)   \n" +
                    " from `报价精品详细表` `报价精品详细表`     \n" +
                    " where `报价单表`.`精品` = `报价精品详细表`.`编号`  and 报价精品详细表.折后价 > 0 ) `精品折后总价` ,  \n" +

                    " (select SUM(IFNULL(`成本`  * 数量,0))   \n" +
                    " from `报价精品详细表` `报价精品详细表`     \n" +
                    " where `报价单表`.`精品` = 报价精品详细表.`编号` and 折后价 = 0 ) `精品赠送成本` ,  \n" +

                    " (select sum(ifnull(ROUND(折后价,2), 0.0) - ifnull(成本, 0.0)) * ifnull(数量, 0) from 报价精品详细表 where `报价单表`.`精品` = 报价精品详细表.编号 and 折后价 <> 0 and origin_shop_type = 'Y') as 'GPS毛利',  \n" +
                    " (select sum(ifnull(ROUND(折后价,2), 0.0) - ifnull(成本, 0.0)) * ifnull(数量, 0) from 报价精品详细表 aa, lsh套餐信息 bb where aa.id = bb.套餐编号 and aa.编号 = `报价单表`.`精品` and aa.折后价 <> 0 and bb.NC编号 = 63) as '玻璃维修险毛利',  \n" +

                    " (select CONCAT(`报价费用表`.`费用折后价`,'|', IFNULL(SUM(IF(报价费用详细表.费用名称='其他服务费', 报价费用详细表.费用原价, 0)),0))     \n" +
                    " from `报价费用表` `报价费用表`,  报价费用详细表 报价费用详细表      \n" +
                    " where `报价单表`.`费用编号` = `报价费用表`.`费用编号`   and  `报价费用表`.`费用编号` = 报价费用详细表.`费用编号` group by `报价费用表`.`费用编号`) `报价费用数据`,   \n" +

                    " (select ifnull(费用折后价, 0) from 报价费用详细表 where 费用编号 = `报价单表`.费用编号 and 报价费用详细表.费用名称='上牌服务费') as '上牌服务费',  \n" +
                    " ((select ifnull(费用折后价, 0) from 报价费用详细表 where 费用编号 = `报价单表`.费用编号 and 报价费用详细表.费用名称='上牌服务费') / 1.17  \n" +
                    "    - (select ifnull(费用原价, 0) from 报价费用详细表 where 费用编号 = `报价单表`.费用编号 and 报价费用详细表.费用名称='上牌服务费成本') / 1.17) as '上牌服务费毛利',  \n" +

                    " (select ifnull(其他一, 0) + ifnull(其他二, 0) + ifnull(其他三, 0) + ifnull(其他四, 0) + ifnull(其他五, 0) from 报价预收表 where 预收编号 = `报价单表`.预收编号) as '其他费用(含税)',  \n" +
                    " (select ifnull(代金券, 0) + ifnull(会员费, 0) + ifnull(购置税, 0) + ifnull(续保服务费, 0)   \n" +
                    "   + ifnull(贷款抵押费, 0) + ifnull(二手车押金, 0) + ifnull(上牌保证金, 0) + ifnull(车辆管理费, 0)  \n" +
                    "   + ifnull(常规保养, 0) + ifnull(其他六, 0) + ifnull(其他七, 0) from 报价预收表 where 预收编号 = `报价单表`.预收编号) as '预收项目(含税)',  \n" +
                    " ifnull((select ifnull(其他一, 0) - ifnull(其他一成本, 0) from 报价预收表 where 预收编号 = `报价单表`.预收编号), 0) / 1.17 as '其他一',  \n" +
                    " ifnull((select ifnull(其他二, 0) - ifnull(其他二成本, 0) from 报价预收表 where 预收编号 = `报价单表`.预收编号), 0) / 1.17 as '其他二',  \n" +
                    " (select ifnull(其他三, 0) / 1.17 from 报价预收表 where 预收编号 = `报价单表`.预收编号) as '其他三',  \n" +
                    " (select ifnull(其他四, 0) / 1.17 from 报价预收表 where 预收编号 = `报价单表`.预收编号) as '其他四',  \n" +
                    " (select ifnull(其他五, 0) / 1.17 from 报价预收表 where 预收编号 = `报价单表`.预收编号) as '其他五',  \n" +

                    "   IF(advance_sales_days is null,'否','是') 是否提前销售,  \n" +
                    "   IF(advance_sales_days is null,null,库存利润表.`上报厂家日期`) 提前销售日期,  \n" +
                    "   advance_sales_days 提前销售天数," +
                    "   route_days  在途天数,\n" +
                    "       (case when (`LSH库存表a`.`生产号` NOT LIKE '%IRISTechNV_%' OR `LSH库存表a`.`生产号` NOT LIKE '%IRISTechnologies%') and NBI车辆状态 = '在途库存'and 车辆零售状态='未卖' then '在途可售' \n" +
                    "           when (`LSH库存表a`.`生产号` NOT LIKE '%IRISTechNV_%' OR `LSH库存表a`.`生产号` NOT LIKE '%IRISTechnologies%') and NBI车辆状态 = '现货库存'and 车辆零售状态='未卖' then '在库可售'\n" +
                    "           when (`LSH库存表a`.`生产号` NOT LIKE '%IRISTechNV_%' OR `LSH库存表a`.`生产号` NOT LIKE '%IRISTechnologies%') and 报价交车表.开票时间 is not null and 报价交车表.`交车日期` is null then '已开票未提车'\n" +
                    "           when (`LSH库存表a`.`生产号` NOT LIKE '%IRISTechNV_%' OR `LSH库存表a`.`生产号` NOT LIKE '%IRISTechnologies%') and NBI车辆状态 = '在途库存' and (车辆零售状态='已卖' or 报价订单表.`订单申请日期` is not null ) then '在途已配'\n" +
                    "           when (`LSH库存表a`.`生产号` NOT LIKE '%IRISTechNV_%' OR `LSH库存表a`.`生产号` NOT LIKE '%IRISTechnologies%') and NBI车辆状态 = '现货库存'and (车辆零售状态='已卖' or 报价订单表.`订单申请日期` is not null ) then '在库已配'" +
                    "   end)    车辆库存状态,  \n" +
                    "   `LSH库存表a`.car_number 车辆编号,  \n" +
                    "   `LSH库存表a`.warehouse 仓库,  \n" +
                    "   `LSH库存表a`.inventory_location 库存位置,  \n" +
                    "   `LSH库存表a`.remark 备注,  \n" +
                    "   (TO_DAYS(DATE_FORMAT(IF(报价交车表.开票时间 is null ,CURDATE(),报价交车表.开票时间),'%Y%m%d')) - TO_DAYS(IFNULL(exchange_stock_log.update_time,报价订单表.`订单申请日期`)) ) 配车天数,\n" +
                    "   IFNULL(exchange_stock_log.update_time,报价订单表.`订单申请日期`)  配车日期,  \n" +
                    // SI的取数，修改为从SNP_SETTING中取，但关联查询的时候不能直接用车型编号
                    // 另外，调过的车没有批售SI
                    " (SELECT concat(IFNULL(a.BMBS_retail_SI, 0.00), '|', if(LSH库存表a.所属经销商 = lsh库存表a.调入经销商, 0.00, IFNULL(a.BMBS_wholesale_SI, 0.00)),'|',IFNULL(a.standard_configuration_area_price_limit_SNP_tax_included,0.00))    \n" +
                    " FROM snp_setting a   \n" +
                    " WHERE a.carCode in (  \n" +
                    " 	select 车型编号  \n" +
                    " 	from lsh车型  \n" +
                    " 	where (来源, 品牌, 级别, 车型, 车型详细) in (  \n" +
                    " 		select 来源, 品牌, 级别, 车型, 车型详细   \n" +
                    " 		from lsh车型   \n" +
                    " 		where 车型编号 = (select 车型编号 from lsh库存表 where 库存编号 = `LSH库存表a`.库存编号)  \n" +
                    " 	)  \n" +
                    " )   \n" +
                    " and a.permit = `LSH库存表a`.所属经销商  \n" +
                    " and a.y_m = '190001'   \n" +
                    " AND (a.apperanceCode in (select 颜色代码 FROM lsh库存表 where 库存编号 = `LSH库存表a`.库存编号) or a.apperanceCode = 0)   \n" +
                    " ORDER BY a.apperanceCode DESC   \n" +
                    " LIMIT 1  \n" +
                    " ) snp_setting,  \n" +
                    " LSH库存表a.调入经销商, LSH库存表a.所属经销商 as 所属经销商GP, LSH库存表a.协商调车款项金额  AS '调车协商价格' ,   \n" +
                    " IF(((`lsh保险申请附属`.`状态` ='04' or `lsh保险申请附属`.`状态` ='03') and 报价单表.`保险编号` is not null) or  报价单表.`保险编号` is  null  ,'否','是') `是否保险` ,  \n" +
                    " IF(((`lsh金融申请`.`状态` ='04' or `lsh金融申请`.`状态` ='03' or `lsh金融申请`.`状态`='12') and 报价单表.`金融` is not null) or  报价单表.`金融` is  null  ,'否','是') `是否金融` ,  \n" +
                    " `lsh金融申请附属`.贴息金额,   " +
                    " ROUND(`报价单表`.`折后价` /1.17,2) AS '车辆销售价(未税)',\n" +
                    " IFNULL((select cast(sum(随车成本) as decimal) from 报价随车表 where 报价编号 = `报价单表`.`报价编号` and 随车类型 = '厂家'), 0) as '厂家随车成本总价',\n" +
                    " IFNULL((select cast(sum(厂家返利) as decimal) from 报价随车表 where 报价编号 = 报价单表.报价编号 and 随车类型 = '厂家'), 0) as '厂家随车实收总价',\n" +
                    " `报价五星延保`.`延保折后价` AS '延保实收金额（含税）',\n" +
                    " `报价五星延保`.`延保原价` AS '延保成本（含税）',\n" +
                    " ifnull((select cast(ifnull(厂家延保实收, 0) - ifnull(厂家延保MSRP, 0) as decimal)    from 报价五星延保 where 延保编号 = 报价单表.延保编号 and 厂家延保实收 <> 0), 0) / 1.17 as '现金销售厂家延保毛利', " +
                    " ifnull((select cast(ifnull(延保折后价, 0) - ifnull(延保原价, 0) as decimal)          from 报价五星延保 where 延保编号 = 报价单表.延保编号 and 延保折后价 <> 0), 0) / 1.17 as '现金销售本店延保毛利', " +
                    " ifnull((select cast(ifnull(第三方延保实收, 0) - ifnull(第三方延保MSRP, 0) as decimal) from 报价五星延保 where 延保编号 = 报价单表.延保编号 and 第三方延保实收 <> 0), 0) / 1.17 as '现金销售第三方延保毛利', " +
                    "  ifnull((select sum(ifnull(ROUND(折后价,2), 0) * ifnull(数量, 0))                     from 报价精品详细表 where 编号 = 报价单表.精品 and 折后价 > 0), 0) as '精品销售总价', " +
                    "  ifnull((select sum((ifnull(ROUND(折后价,2), 0) - ifnull(原价, 0)) * ifnull(数量, 0)) from 报价精品详细表 where 编号 = 报价单表.精品 and 折后价 > 0), 0) as '现金销售精品产品毛利' " +
                    // 这里将原来的`LSH库存表`修改为`LSH库存表a`，是为了与SNP_SETTING的取数兼容
                    // 测试过程中发现，如果不改名字，报错信息提示返回结果多于一行，但改名后，正常返回
                    " FROM `LSH库存表` `LSH库存表a`   \n" +
                    " LEFT JOIN `LSH车型`  ON `LSH车型`.`车型编号` = `LSH库存表a`.`车型编号`     \n" +
                    " LEFT JOIN  (     \n" +
                    " select a.*         \n" +
                    "     FROM (      \n" +
                    " 	    SELECT a.* , b.`订单状态` 订单状态   \n" +
                    " 		    FROM 报价单表 a , 报价订单表  b    \n" +
                    " 	    where A.许可=:dealer and a.订单编号=b.`订单编号`    \n" +
                    " 	    and 是否驳回 = '否'  order by  `报价编号` desc    \n" +
                    " 		) a group by a.初始报价单号    \n" +
                    "    ) 报价单表 ON `报价单表`.`库存编号`=  `LSH库存表a`.库存编号     \n" +
                    " and 报价单表.订单状态 in ( '意向订单O','意向订单C', '订单修改' , '订单C' , '订单O' , '合同O' , '合同C' , '开票O' , '开票C' , '换票C' , '换票O' , '退票O' , '合同修改' , '换票','退订','退订R' )    \n" +
                    "   LEFT JOIN 顾客记录汇总 ON 顾客记录汇总.顾客编号=报价单表.`顾客编号`  \n" +
                    " LEFT JOIN  `报价金融表` ON `报价金融表`.`金融编号` = `报价单表`.`金融`     \n" +
                    " LEFT JOIN `报价交车表` ON `报价单表`.`交车` = `报价交车表`.`编号`     \n" +
                    " LEFT JOIN  `报价客户表` ON `报价单表`.`客户详细` = `报价客户表`.`编号`     \n" +
                    " LEFT JOIN `报价置换表` ON `报价单表`.`置换编号` = `报价置换表`.`置换编号`     \n" +
                    " LEFT JOIN `报价订单表` ON `报价单表`.`订单编号` = `报价订单表`.`订单编号`     \n" +
                    " LEFT JOIN `报价五星延保` ON `报价单表`.`延保编号` = `报价五星延保`.`延保编号`     \n" +
                    " LEFT JOIN `报价随车表` ON `报价随车表`.`报价编号` = `报价单表`.`报价编号`     \n" +
                    " LEFT JOIN `报价付款详细` ON `报价单表`.`报价编号` = `报价付款详细`.`报价编号` AND `付款事由` = '总计'    \n" +
                    " LEFT JOIN `库存利润表` ON `LSH库存表a`.`库存编号` = `库存利润表`.`库存编号`     \n" +
                    " LEFT JOIN `LSH经销商表` `所属经销商` ON 所属经销商.`许可_BMBS` = `LSH库存表a`.`所属经销商`     \n" +
                    " LEFT JOIN `LSH经销商表` `采购批发商` ON 采购批发商.`许可_BMBS` = `LSH库存表a`.`采购批发商`     \n" +
                    " LEFT JOIN `LSH库存内部可见` ON `LSH库存内部可见`.`库存编号` = `LSH库存表a`.`库存编号` AND (`内部可见角色` = 'SC/CDO可见'  OR `内部可见角色` = '')    \n" +
                    " LEFT JOIN `用户` ON `用户`.`许可` = `LSH库存表a`.`所属经销商` AND `用户`.`用户名` = `报价单表`.`销售顾问`     \n" +
                    " LEFT JOIN income_adjustment ON `LSH库存表a`.库存编号 = income_adjustment.stock_code   \n" +
                    " LEFT JOIN  `lsh保险申请附属`  lsh保险申请附属  on lsh保险申请附属.`初始报价单号`=报价单表.`初始报价单号`   \n" +
                    " LEFT JOIN  `lsh金融申请`  lsh金融申请  ON  lsh金融申请.`初始报价单号`=报价单表.`初始报价单号`  \n" +
                    " LEFT JOIN  `lsh金融申请附属`   `lsh金融申请附属` ON  `lsh金融申请附属` .`初始报价单号`=报价单表.`初始报价单号`  \n" +
                    " LEFT JOIN  (select org_qid,MAX(update_time) update_time from exchange_stock_log group by org_qid ) exchange_stock_log ON exchange_stock_log.org_qid = 报价单表.`初始报价单号` \n" +
                    "   \n" +
                    " WHERE  ((`LSH库存表a`.所属经销商= :dealer )   \n" +
                    " 	        OR (`LSH库存表a`.调入经销商= :dealer and `LSH库存表a`.调车物流状态 is not null and `LSH库存表a`.调车物流状态 <>'')     \n" +
                    " 	        OR (`LSH库存表a`.调出经销商=:dealer and `LSH库存表a`.调车物流状态='调出出库'  ))  \n" +
                    " and (`LSH库存表a`.is_delete = '0' or `LSH库存表a`.is_delete is null)  \n" +
                    " GROUP BY LSH库存表a.车架号, `报价单表`.`报价编号`) aa where 1 = 1  \n";

            Map params = Maps.newHashMap();
            params.put("totalCount", map.get("totalCount") == null ? 0 : map.get("totalCount"));
            map.remove("totalCount");
            params.put("dealer", dealer);
            Iterator<Map.Entry<String, String>> it = map.entrySet().iterator();
            while (it.hasNext()) {
                Map.Entry<String, String> entry = it.next();
                //过滤掉前端传回的参数map中 "1":"1" 的情况
                if ("1".equals(entry.getKey())) {
                    sql += "  and (aa.car_state is null  or aa.car_state ='00')   ";
                    continue;
                }
                if ((!"订单签署日期1".equals(entry.getKey()) && !"订单签署日期2".equals(entry.getKey())) &&
                        (!"交车日期1".equals(entry.getKey()) && !"交车日期2".equals(entry.getKey())) &&
                        (!"开票日期1".equals(entry.getKey()) && !"开票日期2".equals(entry.getKey()))) {
                    sql += " AND aa.`" + entry.getKey() + "` like '%" + entry.getValue() + "%' ";
                } else if ("订单签署日期1".equals(entry.getKey())) {
                    sql += " AND date_format(aa.`订单签署日期`,'%Y-%m-%d') >=date_format('" + entry.getValue() + "','%Y-%m-%d') ";
                } else if ("订单签署日期2".equals(entry.getKey())) {
                    sql += " AND date_format(aa.`订单签署日期`,'%Y-%m-%d') <=date_format('" + entry.getValue() + "','%Y-%m-%d')  ";
                } else if ("交车日期1".equals(entry.getKey())) {
                    sql += " AND date_format(aa.`交车日期`,'%Y-%m-%d') >=date_format('" + entry.getValue() + "','%Y-%m-%d')  ";
                } else if ("交车日期2".equals(entry.getKey())) {
                    sql += " AND date_format(aa.`交车日期`,'%Y-%m-%d') <=date_format('" + entry.getValue() + "','%Y-%m-%d')  ";
                } else if ("开票日期1".equals(entry.getKey())) {
                    sql += " AND date_format(aa.`开票日期`,'%Y-%m-%d') >=date_format('" + entry.getValue() + "','%Y-%m-%d')  ";
                } else if ("开票日期2".equals(entry.getKey())) {
                    sql += " AND date_format(aa.`开票日期`,'%Y-%m-%d') <=date_format('" + entry.getValue() + "','%Y-%m-%d')  ";
                }

                if ("car_state".equals(entry.getKey())) {
                    if (entry.getValue() == null || "".equals(entry.getValue())) {
                        sql += "  and (aa.car_state is null  or aa.car_state ='00')   ";
                    } else {
                        if ("00".equals(entry.getValue())) {
                            sql += "  and (aa.car_state is null  or aa.car_state ='00')   ";
                        } else {
                            sql += "and  aa.car_state ='01' ";
                        }
                    }
                }
            }

            sql += " ORDER BY aa.`开票日期` DESC, aa.`订单审批通过日期` DESC, aa.`采购日期`DESC, aa.`生产日期` DESC, aa.`生产号` DESC";

            PageData<Map<String, Object>> pageData = this.getPage(sql, params, request.getPageSize(), request.getPageIndex());
            List<Map<String, Object>> dataes = pageData.getData();

            // 找到调整后的SI
            List siList = getAdjustedSi(dealer);
            // 找到调整后的其他费用
            List feeList = getAdjustedFee(dealer);

            double dValue = 0d;
            if (dataes != null) {
                for (Map<String, Object> data : dataes) {
                    //将查询中关于SNP部分进行分解
                    this.splitQuotationsnp(data);

                    //将查询中关于保险部分进行分解
                    this.splitQuotationInsu(data);

                    //将查询中关于费用部分进行分解
                    this.splitQuotationExpense(data);

                    //将查询中关于精品部分进行分解
                    this.splitQuotationChoicest(data);

                    data.put("新保手续费", toDouble(data.get("保险批单费")) / 1.06 + toDouble(data.get("新保手续费")) / 1.06);  // dch的手续费率为1.06,与lsh不同

                    data.put("经销商随车成本", (Double) data.get("经销商赠送成本不含延保精品") + toDouble(data.get("精品赠送成本")) / 1.17 + toDouble(data.get("延保赠送成本")) / 1.17);
                    data.put("经销商销售部赠送成本", data.get("经销商随车成本"));

                    // 对于每一辆车，去siList寻找相应的调整后SI，如果找到覆盖原KEY-VALUE，未找到保留原KEY-VALUE
                    for (Object o : siList) {
                        Map m = (Map) o;
                        if (data.get("库存编号").equals(m.get("stock_id"))) { // 库存编号匹配
                            Integer siId = (Integer) m.get("si_setting_id");
                            BigDecimal bdSiAmount = (BigDecimal) m.get("si_amount");
                            Double siAmount = bdSiAmount.doubleValue();
                            switch (siId) { // 根据不同的调整项设置值
                                case 10:
                                    data.put("厂家SI_保险实收", siAmount);
                                    break;
                                case 16:
                                    data.put("厂家SI_购置税实收", siAmount);
                                    break;
                                case 27:
                                    data.put("厂家SI_SSSC实收", siAmount);
                                    break;
                                case 18:
                                    data.put("厂家SI_旅游礼包实收", siAmount);
                                    break;
                                case 32:
                                    data.put("厂家SI_SD卡实收", siAmount);
                                    break;
                                case 9:
                                    data.put("厂家SI_加油卡实收", siAmount);
                                    break;
                                case 28:
                                    data.put("厂家SI_实物礼品实收", siAmount);
                                    break;
                                case 6:
                                    data.put("贴息金额", siAmount);
                                case 19:
                                    data.put("厂家SI_服务礼券实收", siAmount);
                                    break;
                                case 24:
                                    if (data.get("调入经销商").equals(data.get("所属经销商"))) { // 调过的车没有批发SI
                                        data.put("厂家批售SI", 0.00);
                                    } else {
                                        data.put("厂家批售SI", siAmount);
                                    }
                                    break;
                                default: // 其他的零售SI调整全部加起来
                                    data.put("厂家零售SI", (Double) data.get("厂家零售SI") + siAmount);
                            }
                        }
                    }

                    // 对于每一辆车，去feeList寻找相应的调整后费用，如果找到覆盖原KEY-VALUE，未找到保留原KEY-VALUE
                    BigDecimal bdZero = new BigDecimal("0.00");
                    for (Object o : feeList) {
                        Map m = (Map) o;
                        if (data.get("库存编号").equals(m.get("stock_code"))) { // 库存编号匹配
                            BigDecimal bdFiPoRe = m.get("finance_poundage_adjust_real") == null ? bdZero : (BigDecimal) m.get("finance_poundage_adjust_real");
                            BigDecimal bdInPoRe = m.get("insurance_poundage_adjust_real") == null ? bdZero : (BigDecimal) m.get("insurance_poundage_adjust_real");
                            BigDecimal bdAfMaPoCoAd = m.get("after_market_product_cost_adjust") == null ? bdZero : (BigDecimal) m.get("after_market_product_cost_adjust");
                            BigDecimal bdAfMaPoCoBuAd = m.get("after_market_product_cost_buy_adjust") == null ? bdZero : (BigDecimal) m.get("after_market_product_cost_buy_adjust");
                            BigDecimal bdFiStExCoRe = m.get("five_stars_extend_cost_adjust_real") == null ? bdZero : (BigDecimal) m.get("five_stars_extend_cost_adjust_real");
                            BigDecimal bdFiStExCoAd = m.get("five_stars_extend_cost_adjust") == null ? bdZero : (BigDecimal) m.get("five_stars_extend_cost_adjust");
                            BigDecimal bdThExCoRe = m.get("third_extend_cost_adjust_real") == null ? bdZero : (BigDecimal) m.get("third_extend_cost_adjust_real");
                            BigDecimal bdThExCoAd = m.get("third_extend_cost_adjust") == null ? bdZero : (BigDecimal) m.get("third_extend_cost_adjust");
                            data.put("金融手续费", bdFiPoRe.doubleValue());
                            data.put("新保手续费", bdInPoRe.doubleValue());
                            data.put("经销商随车成本", (Double) data.get("经销商随车成本") + bdAfMaPoCoAd.doubleValue()); // 随车精品成本调整
                            // 三种延保都是非送即卖，计算调整的时候要么在 经销商随车成本 中，要么在 现金销售五星延保毛利 中（只有厂家和第三方有调整）
                            if (((BigDecimal) data.get("厂家延保实收金额(含税)")).compareTo(bdZero) == 0) {
                                // 经销商随车成本 包含 后市场赠送成本调整 + 售价为0的五星延保成本调整
                                data.put("经销商随车成本", (Double) data.get("经销商随车成本") + bdFiStExCoAd.doubleValue());
                            } else {
                                // 现金销售五星延保毛利 包含 该车五星延保售价不为0的成本调整
                               // data.put("现金销售五星延保毛利", (Double) data.get("现金销售五星延保毛利") + bdFiStExCoAd.doubleValue());
                            }
                            if (((BigDecimal) data.get("第三方延保实收金额(含税)")).compareTo(bdZero) == 0) {
                                // 经销商随车成本 包含 后市场赠送成本调整 + 售价为0的五星延保成本调整
                                data.put("经销商随车成本", (Double) data.get("经销商随车成本") + bdThExCoAd.doubleValue());
                            } else {
                                // 现金销售五星延保毛利 包含 该车五星延保售价不为0的成本调整
                             //   data.put("现金销售五星延保毛利", (Double) data.get("现金销售五星延保毛利") + bdThExCoAd.doubleValue());
                            }
                            data.put("现金销售后市场产品毛利", (Double) data.get("现金销售后市场产品毛利") + bdAfMaPoCoBuAd.doubleValue());

                            data.put("厂家延保成本(含税)", bdFiStExCoRe);
                            data.put("第三方延保成本(含税)", bdThExCoRe);
                            if (((BigDecimal) data.get("厂家延保实收金额(含税)")).compareTo(bdZero) == 0) { // 作为随车时
                                data.put("现金销售厂家延保毛利", 0.00); // 没有毛利
                            } else { // 非随车时
                                BigDecimal income = ((BigDecimal) data.get("厂家延保实收金额(含税)"));
                                BigDecimal cost = (BigDecimal) data.get("厂家延保成本(含税)");
                                data.put("现金销售厂家延保毛利", income.subtract(cost)); // 毛利
                            }
                            if (((BigDecimal) data.get("第三方延保实收金额(含税)")).compareTo(bdZero) == 0) { // 作为随车时
                                data.put("现金销售第三方延保毛利", 0.00); // 没有毛利
                            } else { // 非随车时
                                BigDecimal income = (BigDecimal) data.get("第三方延保实收金额(含税)");
                                BigDecimal cost = (BigDecimal) data.get("第三方延保成本(含税)");
                                data.put("现金销售第三方延保毛利", income.subtract(cost));
                            }
                        }
                    }
                    BigDecimal b1 = new BigDecimal(Double.parseDouble(data.get("现金销售厂家延保毛利").toString()));
                    BigDecimal b2 = new BigDecimal(Double.parseDouble(data.get("现金销售第三方延保毛利").toString()));
                    data.put("延保毛利", b1.add(b2).doubleValue());
//                    data.put("延保毛利", ((BigDecimal) data.get("现金销售厂家延保毛利")).add((BigDecimal) data.get("现金销售第三方延保毛利")).doubleValue());

                    //车辆落地价（不含购置税）计算,是否金融条件变更
                    if ("否".equals(data.get("是否金融"))) {
                        dValue = toDouble(data, "折后价");
                        data.put("车辆尾款（顾客）", toDouble(data, "折后价") - toDouble(data, "订金金额_ORI"));
                    } else {
                        dValue = toDouble(data, "首付额") + toDouble(data, "金融服务费") + toDouble(data, "保证金金额");
                        data.put("车辆尾款（顾客）", toDouble(data, "首付额") + toDouble(data, "保证金金额") - toDouble(data, "订金金额_ORI"));
                    }

                    data.put("车辆落地价（不含购置税）", dValue + toDouble(data, "保险小计") + toDouble(data, "精品折后价") + toDouble(data, "延保折后价")
                            + toDouble(data, "费用折后价") - toDouble(data, "置换估价") - toDouble(data, "经销商_保险成本") - toDouble(data, "经销商SI_贴息成本")
                            - toDouble(data, "厂家SI_贴息成本")
                    );

                    //计算GP
                    Map<GPUtils.ParameterName, Double> gpParameters = new HashMap<>();
                    gpParameters.put(GPUtils.ParameterName.车辆销售价, toDouble(data.get("折后价")));
                    gpParameters.put(GPUtils.ParameterName.消费税, toDouble(data.get("消费税")));
                    String inDealer = (String) data.get("调入经销商");
                    String beDealer = (String) data.get("所属经销商GP");
                    // 调过的车，调入经销商与所属经销商GP相同
                    boolean isTransferred = inDealer != null && beDealer != null && inDealer.equals(beDealer);
                    if (isTransferred) {
                        gpParameters.put(GPUtils.ParameterName.采购价格, toDouble(data.get("调车协商价格")));
                    } else {
                        gpParameters.put(GPUtils.ParameterName.采购价格, toDouble(data.get("采购价格（含税）")));
                    }
                    gpParameters.put(GPUtils.ParameterName.经销商随车成本, toDouble(data.get("经销商随车成本")));
                    gpParameters.put(GPUtils.ParameterName.精品赠送成本, toDouble(data.get("精品赠送成本")));
                    gpParameters.put(GPUtils.ParameterName.延保赠送成本, toDouble(data.get("延保赠送成本")));
                    gpParameters.put(GPUtils.ParameterName.厂商非购置税随车成本, toDouble(data.get("厂商非购置税随车成本")));
                    gpParameters.put(GPUtils.ParameterName.厂家批售SI, toDouble(data.get("厂家批售SI")));
                    gpParameters.put(GPUtils.ParameterName.厂家零售SI, toDouble(data.get("厂家零售SI")));
                    gpParameters.put(GPUtils.ParameterName.精品毛利, toDouble(data.get("现金销售后市场产品毛利")));
                    gpParameters.put(GPUtils.ParameterName.金融手续费, toDouble(data.get("金融手续费")));
                    gpParameters.put(GPUtils.ParameterName.金融服务费, toDouble(data.get("金融服务费")));
                    gpParameters.put(GPUtils.ParameterName.后市场销售价, toDouble(data.get("精品折后价_合计")));
                    gpParameters.put(GPUtils.ParameterName.后市场成本, toDouble(data.get("精品成本_合计")));
//                    gpParameters.put(GPUtils.ParameterName.五星延保销售价, toDouble(data.get("延保折后价")));
                    gpParameters.put(GPUtils.ParameterName.厂家SI_购置税成本, toDouble(data.get("厂家SI_购置税成本")));
                    gpParameters.put(GPUtils.ParameterName.贴息金额, toDouble(data.get("贴息金额")));
                    gpParameters.put(GPUtils.ParameterName.延保毛利, toDouble(data.get("延保毛利")));
                    gpParameters.put(GPUtils.ParameterName.交强险手续费, toDouble(data.get("交强险手续费")));
                    gpParameters.put(GPUtils.ParameterName.商业险手续费, toDouble(data.get("商业险手续费")));
                    gpParameters.put(GPUtils.ParameterName.盗抢险手续费, toDouble(data.get("盗抢险手续费")));
                    gpParameters.put(GPUtils.ParameterName.置换手续费, toDouble(data.get("置换手续费")));
                    gpParameters.put(GPUtils.ParameterName.上牌服务费毛利, toDouble(data.get("上牌服务费毛利")));
                    gpParameters.put(GPUtils.ParameterName.其他一毛利, toDouble(data.get("其他一毛利(有成本)")));
                    gpParameters.put(GPUtils.ParameterName.其他二毛利, toDouble(data.get("其他二毛利(有成本)")));
                    gpParameters.put(GPUtils.ParameterName.销售服务费, toDouble(data.get("销售服务费")));
                    gpParameters.put(GPUtils.ParameterName.其他三毛利, toDouble(data.get("其他三毛利(无成本)")));
                    gpParameters.put(GPUtils.ParameterName.其他四毛利, toDouble(data.get("其他四毛利(无成本)")));
                    gpParameters.put(GPUtils.ParameterName.其他五毛利, toDouble(data.get("其他五毛利(无成本)")));
                    gpParameters.put(GPUtils.ParameterName.厂家延保实收金额, toDouble(data.get("厂家延保实收金额(含税)")));
                    gpParameters.put(GPUtils.ParameterName.第三方延保实收金额, toDouble(data.get("第三方延保实收金额(含税)")));
                    gpParameters.put(GPUtils.ParameterName.精品调整成本差值, toDouble(data.get("after_market_product_cost_adjust")));
                    gpParameters.put(GPUtils.ParameterName.金融手续调整成本差值, toDouble(data.get("finance_poundage_adjust")));
                    gpParameters.put(GPUtils.ParameterName.保险手续费调整成本差值, toDouble(data.get("insurance_poundage_adjust")));

//                    gpParameters.put(GPUtils.ParameterName.延保调整成本差值, toDouble(data.get("five_stars_extend_cost_adjust")));
                    if("未卖".equals(String.valueOf(data.get("车辆零售状态")))|| (data.get("开票日期")==null)||"".equals(data.get("开票日期"))){
                        Map<String, Object> gpValues = new HashMap<>();
                        gpValues.put("GP1", "");
                        gpValues.put("GP1%", "");
                        gpValues.put("GP1dot5", "");
                        gpValues.put("GP1dot5%", "");
                        gpValues.put("GP2", "");
                        gpValues.put("GP2%", "");
                        gpValues.put("GP3", "");
                        gpValues.put("GP3%", "");
                        gpValues.put("GP4.5", "");
                        gpValues.put("GP4.5%", "");
                        data.putAll(gpValues);
                    }
                    else{
                        data.putAll(GPUtils.getGPInfo(gpParameters));
                    }
                }
            }

            return pageData;

        } catch (Exception exc) {
            logger.error("Query Exception.", exc);
//            exc.printStackTrace(); // todoDelete
            return null;
        } finally {
            logger.warn("Time Ellapsed = " + (System.currentTimeMillis() - startTime));
        }
    }

    /**
     * 根据许可找到调整后的SI
     *
     * @param dealer 许可
     * @return 包含库存编号，SI类型ID，SI金额的LIST
     */
    private List getAdjustedSi(String dealer) {
        String sql = " select  \n" +
                " 	stock_id,  \n" +
                " 	si_setting_id,  \n" +
                " 	si_amount  \n" +
                " from si_bmbs_setting  \n" +
                " where permit = :dealer  \n" +
                " and set_type = 1  \n";
        Map<String, Object> params = new HashMap<>();
        params.put("dealer", dealer);
        return getList(sql, params);
    }

    /**
     * 根据许可找到调整后的其他费用
     *
     * @param dealer 许可
     * @return 包含库存编号的各调整项
     */
    private List getAdjustedFee(String dealer) {
        String sql = " select  \n" +
                " 	stock_code,  \n" +
                " 	finance_poundage_adjust_real,  \n" +
                " 	insurance_poundage_adjust_real,  \n" +
                " 	after_market_product_cost_adjust,  \n" +
                " 	after_market_product_cost_buy_adjust,  \n" +
                " 	five_stars_extend_cost_adjust_real,  \n" +
                " 	five_stars_extend_cost_adjust,  \n" +
                "   third_extend_cost_adjust_real,  \n" +
                "   third_extend_cost_adjust  \n" +
                " from income_adjustment  \n" +
                " where permit = :dealer  \n" +
                " and type = 1  \n";
        Map<String, Object> params = new HashMap<>();
        params.put("dealer", dealer);
        return getList(sql, params);
    }

    /**
     * 将查询中关于SNP部分进行分解
     *
     * @param data
     */
    private void splitQuotationsnp(Map<String, Object> data) {
        String key = "snp_setting";
        Object quotationSnp = data.get(key);

        String[] fields = new String[]{"BMBS_retail_SI", "BMBS_wholesale_SI","standard_configuration_area_price_limit_SNP_tax_included"};
        if (quotationSnp == null) {
            for (String field : fields) {
                data.put(field, 0.00);
            }
        } else {
            String[] values = quotationSnp.toString().split("[|]");
            for (int index = 0; index < values.length; index++) {
                if (values[index] == null || values[index].trim().equals("")) {
                    data.put(fields[index], 0);
                } else {
                    data.put(fields[index], Double.parseDouble(values[index]));
                }
            }
        }
        // 这里的KEY原来写作“零售SI”，“批售SI”，但前端没有用到
        // 前端用到的是库存利润表的"厂家零售SI"，"厂家批售SI"
        // 为兼容原来的接口，同时保证数据有意义，在此作出修改  -- byDF 20170509
        data.put("厂家零售SI", data.get("BMBS_retail_SI"));
        data.put("厂家批售SI", data.get("BMBS_wholesale_SI"));

        data.put("标配区域限价-SNP（含税）",data.get("standard_configuration_area_price_limit_SNP_tax_included"));

        data.remove(key);
    }

    /**
     * 将查询中关于保险部分进行分解
     *
     * @param data
     */
    private void splitQuotationInsu(Map<String, Object> data) {
        String key = "报价保险";
        Object quotationInsu = data.get(key);

        String[] fields = new String[]{"交强险手续费", "商业险手续费", "保险小计", "新保手续费"};
        if (quotationInsu == null) {
            for (String field : fields) {
                data.put(field, 0);
            }
        } else {
            String[] values = quotationInsu.toString().split("[|]");
            for (int index = 0; index < values.length; index++) {
                if (values[index] == null || values[index].trim().equals("")) {
                    data.put(fields[index], 0);
                } else {
                    data.put(fields[index], Double.parseDouble(values[index]));
                }
            }
        }
        data.remove(key);
    }

    /**
     * 将查询中关于费用部分进行分解
     *
     * @param data
     */
    private void splitQuotationExpense(Map<String, Object> data) {
        String key = "报价费用数据";
        Object quotationExpense = data.get(key);

        String[] fields = new String[]{"费用折后价", "销售服务费"};
        if (quotationExpense == null) {
            for (String field : fields) {
                data.put(field, 0);
            }
        } else {
            String[] values = quotationExpense.toString().split("[|]");
            for (int index = 0; index < values.length; index++) {
                if (values[index] == null || values[index].trim().equals("")) {
                    data.put(fields[index], 0);
                } else {
                    data.put(fields[index], Double.parseDouble(values[index]));
                }
            }
        }

        data.remove(key);
    }

    /**
     * 将查询中关于精品部分进行分解
     *
     * @param data
     */
    private void splitQuotationChoicest(Map<String, Object> data) {
        String key = "精品信息";
        Object quotationChoicest = data.get(key);

        String[] fields = new String[]{"经销商采购价未税", "精品折后价", "精品折后价_合计", "精品成本_合计"};
        if (quotationChoicest == null) {
            for (String field : fields) {
                data.put(field, 0);
            }
        } else {
            String[] values = quotationChoicest.toString().split("[|]");
            for (int index = 0; index < values.length; index++) {
                if (values[index] == null || values[index].trim().equals("")) {
                    data.put(fields[index], 0);
                } else {
                    data.put(fields[index], Double.parseDouble(values[index]));
                }
            }
        }

        data.put("现金销售后市场产品毛利", Double.parseDouble(data.get("精品折后价_合计").toString()) - Double.parseDouble(data.get("精品成本_合计").toString()));
        data.remove(key);
    }

    private double toDouble(Map<String, Object> data, String column) {
        if (!data.containsKey(column)) {
            logger.warn("No column found for " + column);

            return 0;
        }

        Object value = data.get(column);
        if (value == null || value.toString().trim().equals("")) {
            if(logger.isDebugEnabled())
                logger.warn("null value is for column = " + column);

            return 0;
        }

        return Double.parseDouble(value.toString());
    }

    @Override
    @Deprecated
    public PageData getDealerInfo(String dealer, Map map, RequestBase request) {
        String sql = "SELECT * FROM (SELECT a.`品牌` `品牌`, a.`来源`, a.`级别` 车系, a.`车型`, a.`车型详细` 车款, b.`生产日期`, IF ( b.`生产号` \n" +
                "LIKE '%IRISTechNV_%' OR b.`生产号` LIKE '%IRISTechnologies%', '', b.`生产号` ) `生产号`, \n" +
                "IF ( b.`车架号` LIKE '%IRISTechNV_%' OR b.`车架号` LIKE '%IRISTechnologies%', '', b.`车架号` ) `车架号（VIN）`,\n" +
                " b.`欧版底盘号` `欧版底盘号（FIN)`, b.`发动机号`, b.`VSB号`, b.`颜色代码`, b.`颜色`, b.`内饰代码`, b.`内饰`, b.`原厂配置`, \n" +
                "b.`加配配置`, b.`加配价格含税` `加配价格（含税）`, b.`采购价格含税` `采购价格（含税）`, b.`采购价格未税` `采购价格（不含税）`, \n" +
                "a.`MSRP` `BI车款标配MSRP（含税）`, b.`标配MSRP含税` `标配MSRP（含税）`, b.`实际MSRP含税` `实际MSRP（含税）`, b.`SNP含税` `标配区域限价-SNP（含税）`, \n" +
                "b.`厂家确认订单日期`, b.`预计到港日期` `预计到港日期（ETA）`, b.`实际到港日期` `实际到港日期（ATA）`, b.`预计下线日期`, b.`港口代码`, b.`港口名称`, \n" +
                "b.`车辆物流状态`, b.`车辆零售状态`, b.`车辆零售类型`, b.`HOLD日期`, b.`申请释放日期`, b.`批发日期`, b.`批发付款到期日`, b.`NBI车辆状态`, b.`预计到店日期`, \n" +
                "b.`店内车辆位置`, b.`二次PDI完成日期`, b.`二次PDI结果`, b.`入库日期`, b.`出库日期`, b.`库龄天数`, b.`建议三次PDI日期`, b.`三次PDI完成日期` `三次PDI 完成日期`,\n" +
                " b.`三次PDI结果` `三次PDI 结果`, b.`车辆受损情况`, b.`商检单到店状态`, b.`关单到店状态`, b.`合格证到店状态`, b.`合格证号`, b.`商检单号`, b.`关单号`, b.`批发商`,\n" +
                " x.`经销商名` `采购经销商`, j.`经销商名` `所属经销商`, b.`外部可见级别`, w.`内部可见角色` `内部可见级别`, f.`上牌类型`, h.`订单客户姓名` `订单客户`, f.`邮箱` `顾客email`, \n" +
                "IF ( f.`身份证类型` = '身份证', f.`身份证`, '' ) `顾客身份证号码`, IF ( f.`身份证类型` = '外籍ID', f.`身份证`, '' ) `顾客外籍ID号码`, \n" +
                "IF ( f.`身份证类型` = '组织结构', f.`身份证`, '' ) `组织机构代码`, g.`首次上牌日期` `置换车辆上牌年月`, IF ( e.`订单编号` IS NULL, '', \n" +
                "IF ( e.`金融` IS NULL, IFNULL(e.`折后价`, 0), IFNULL(l.`首付额`, 0) + IFNULL(l.`金融服务费`, 0) + IFNULL(l.`保证金金额`, 0)) + IFNULL(q.`保险小计`, 0) + \n" +
                "IFNULL(m.`精品折后价`, 0) + IFNULL(n.`延保折后价`, 0) + IFNULL(o.`费用折后价`, 0) - IFNULL(g.`置换估价`, 0) - IFNULL(t1.`成本`, 0) - IFNULL(t9.`成本`, 0) - \n" +
                "IFNULL(t10.`成本`, 0) - IFNULL(t11.`成本`, 0)) `车辆落地价（不含购置税）`, IFNULL(e.`原价`, 0) - IFNULL(e.`折后价`, 0) `车辆现金折扣`, e.`折后价` `车辆销售价`, \n" +
                "g.`置换估价`, n.`延保原价` `五星延保MSRP（含税）`, n.`延保折后价` `五星延保实收金额（含税）`, n.`延保原价` `五星延保成本（含税）`, h.`订单流水号` `Case号`, h.`订单号`, \n" +
                "IF ( h.`订金类型` = '意向金', h.`订金金额`, '' ) `意向金金额`, \n" +
                "IF ( h.`订金类型` = '订金', h.`订金金额`, '' ) `订金金额`, h.`无息退款天数` `意向金无息退款天数`, h.`资源释放期限` `释放资源期限`, h.`申请修改原因`, h.`撤销订单原因`, \n" +
                "IF ( e.`金融` IS NULL, e.`折后价`, l.`首付额` + l.`保证金金额` ) - h.`订金金额` `车辆尾款（顾客）`, \n" +
                "' ' `代付款`, r.`应付金额` `应收金额`, r.`实付金额` `实收金额`, d.`销售价格`, d.`不含税价`, \n" +
                "h.`订单审批通过日期`, h.`订单签署日期`, h.`订金交付日期`, h.`订单失销日期`, h.`退订订金金额` `退订金金额`, h.`退订金转账日期`, h.`订单修改日期`, \n" +
                "h.`订金加减金额` `订金+/-金额`, h.`订金加减日期` `订金+/-日期`, d.`合同签约日期` `合同签署日期`, d.`开票时间` `开票日期`, d.`合同金额`, d.`余款金额`,\n" +
                " h.`预计开票日期` `合同交货日期`, d.`交车日期`, d.`上牌省` `上牌省份`, d.`上牌市` `上牌城市`, d.`上牌区` `上牌行政区`, d.`上牌地址` `上牌具体地址`, \n" +
                "d.`上牌日期`, d.`牌照号`, IF ( e.`置换编号` IS NULL, '', '是' ) `是否置换`, g.`置换类型` `店内置换/店外置换`, g.`置换估价` `二手车价格`, d.`开票客户姓名` `车主/开票人名称`, \n" +
                "IF ( d.`开票客户身份证类型` = '身份证', d.`开票客户身份证`, '' ) `车主/开票人身份证号码`, IF ( d.`开票客户身份证类型` = '外籍ID', d.`开票客户身份证`, '' ) `外籍车主车主/开票人ID号码`, \n" +
                "d.`开票客户机构代码` `车主/开票人组织机构代码`, d.`开票客户电话` `车主/开票人电话号码`, d.`提车代理人`, d.`提车代理人身份证`, ' ' `外籍提车代理人ID号码`, \n" +
                "IF ( d.`交车日期` IS NULL, '', d.`售后联系人` ) `售后联系人1`, IF ( d.`交车日期` IS NULL, '', d.`售后联系人关系` ) `售后联系人1身份`, \n" +
                "IF ( d.`交车日期` IS NULL, '', d.`售后联系人电话` ) `售后联系人1电话号码`, IF ( d.`交车日期` IS NULL, '', d.`售后联系人地址` ) `售后联系人1联系地址`, \n" +
                "IF ( d.`交车日期` IS NULL, '', d.`售后联系人微信` ) `售后联系人1微信ID`, v.`上报厂家`, v.`上报厂家日期`, v.`上报SIMS`, v.`上报SIMS日期`, ' ' `GP1`, \n" +
                "' ' `GP1%`, s.`经销商随车成本`, t1.`成本` `厂家SI_保险成本`, t2.`成本` `厂家SI_购置税成本`, t3.`成本` `厂家SI_SSSC成本`, t4.`成本` `厂家SI_旅游礼包成本`, \n" +
                "t5.`成本` `厂家SI_SD卡成本`, t6.`成本` `厂家SI_加油卡成本`, t7.`成本` `厂家SI_实物礼品成本`, t8.`成本` `厂家SI_服务礼券成本`, ' ' `GP2`, \n" +
                "' ' `GP2%`, t1.`实收` `厂家SI_保险实收`, t2.`实收` `厂家SI_购置税实收`, t3.`实收` `厂家SI_SSSC实收`, t4.`实收` `厂家SI_旅游礼包实收`, \n" +
                "t5.`实收` `厂家SI_SD卡实收`, t6.`实收` `厂家SI_加油卡实收`, t7.`实收` `厂家SI_实物礼品实收`, t8.`实收` `厂家SI_服务礼券实收`, v.`零售SI` `厂家零售SI`, \n" +
                "v.`批发SI` `厂家批售SI`, ' ' `GP3`, ' ' `GP3%`, p.`上牌服务费`, p.`其他服务费`, n.`延保折后价` `现金销售五星延保毛利`, c.`折后价` - c.`成本` `现金销售后市场产品毛利`, \n" +
                "v.`后市场产品毛利` `后市场毛利`, l.`金融手续费`, u.`交强险手续费` + u.`商业险手续费` `新保手续费`, g.`置换评估费` `置换手续费`, ' ' `Sales T.GP`, ' ' `Sales T.GP%`, \n" +
                "y.`人员姓名` `销售顾问`, b.`库存编号` FROM `LSH库存表` b LEFT JOIN `报价单表` e ON e.`报价编号` = b.`报价编号` LEFT JOIN `LSH车型` a ON b.`车型编号` = a.`车型编号` \n" +
                "LEFT JOIN `报价交车表` d ON e.`交车` = d.`编号` LEFT JOIN `报价客户表` f ON e.`客户详细` = f.`编号` LEFT JOIN `报价置换表` g ON e.`置换编号` = g.`置换编号` \n" +
                "LEFT JOIN `报价订单表` h ON e.`订单编号` = h.`订单编号` LEFT JOIN `报价金融表` l ON e.`金融` = l.`金融编号` LEFT JOIN `报价精品表` m ON e.`精品` = m.`编号` \n" +
                "LEFT JOIN ( SELECT 编号, SUM(折后价) 折后价, SUM(成本) 成本 FROM `报价精品详细表` GROUP BY 编号 ) c ON e.`精品` = c.`编号` LEFT JOIN `报价五星延保` n ON e.`延保编号` = n.`延保编号` \n" +
                "LEFT JOIN `报价费用表` o ON e.`费用编号` = o.`费用编号` LEFT JOIN `报价保险表` u ON e.`保险编号` = u.`保险编号` AND u.`投保类型` = '新保' \n" +
                "LEFT JOIN ( SELECT 费用详细编号, 费用编号, SUBSTRING_INDEX( GROUP_CONCAT(IFNULL(费用原价,0)), ',', 1 ) 上牌服务费, SUBSTRING_INDEX( GROUP_CONCAT(IFNULL(费用原价,0)), ',' ,- 1 ) 其他服务费 \n" +
                "FROM 报价费用详细表 WHERE 费用名称 = '其他服务费' OR 费用名称 = '上牌服务费' GROUP BY 费用编号 ) p ON e.`费用编号` = p.`费用编号` LEFT JOIN ( \n" +
                "SELECT 保险编号, SUM(折后价) 保险小计 FROM `报价保险详细表` WHERE `险种` <> '车船税' GROUP BY 保险编号 ) q ON e.`保险编号` = q.`保险编号` LEFT JOIN `报价付款详细` r \n" +
                "ON e.`报价编号` = r.`报价编号` AND r.`付款事由` = '总计' LEFT JOIN ( SELECT 报价编号, SUM(随车成本) 经销商随车成本 FROM `报价随车表` WHERE 随车类型 = '经销商' \n" +
                "GROUP BY 报价编号 ) s ON s.`报价编号` = e.`报价编号` LEFT JOIN ( SELECT 报价编号, 随车成本 成本, 厂家返利 实收 FROM `报价随车表` WHERE 随车类型 = '厂家' \n" +
                "AND 随车名称 = '保险' ) t1 ON t1.`报价编号` = e.`报价编号` LEFT JOIN ( SELECT 报价编号, 随车成本 成本, 厂家返利 实收 FROM `报价随车表` WHERE 随车类型 = '厂家' \n" +
                "AND 随车名称 = '购置税' ) t2 ON t2.`报价编号` = e.`报价编号` LEFT JOIN ( SELECT 报价编号, 随车成本 成本, 厂家返利 实收 FROM `报价随车表` WHERE 随车类型 = '厂家' \n" +
                "AND 随车名称 = 'SSSC' ) t3 ON t3.`报价编号` = e.`报价编号` LEFT JOIN ( SELECT 报价编号, 随车成本 成本, 厂家返利 实收 FROM `报价随车表` WHERE 随车类型 = '厂家' \n" +
                "AND 随车名称 = '旅游礼包' ) t4 ON t4.`报价编号` = e.`报价编号` LEFT JOIN ( SELECT 报价编号, 随车成本 成本, 厂家返利 实收 FROM `报价随车表` WHERE 随车类型 = '厂家' \n" +
                "AND 随车名称 = 'SD卡' ) t5 ON t5.`报价编号` = e.`报价编号` LEFT JOIN ( SELECT 报价编号, 随车成本 成本, 厂家返利 实收 FROM `报价随车表` WHERE 随车类型 = '厂家' \n" +
                "AND 随车名称 = '加油卡' ) t6 ON t6.`报价编号` = e.`报价编号` LEFT JOIN ( SELECT 报价编号, 随车成本 成本, 厂家返利 实收 FROM `报价随车表` WHERE 随车类型 = '厂家' \n" +
                "AND 随车名称 = '实物礼品' ) t7 ON t7.`报价编号` = e.`报价编号` LEFT JOIN ( SELECT 报价编号, 随车成本 成本, 厂家返利 实收 FROM `报价随车表` WHERE 随车类型 = '厂家' \n" +
                "AND 随车名称 = '服务礼券' ) t8 ON t8.`报价编号` = e.`报价编号` LEFT JOIN ( SELECT 报价编号, 随车成本 成本 FROM `报价随车表` WHERE 随车类型 = '经销商' AND 随车名称 = '保险' ) t9 \n" +
                "ON t9.`报价编号` = e.`报价编号` LEFT JOIN ( SELECT 报价编号, 随车成本 成本 FROM `报价随车表` WHERE 随车类型 = '经销商' AND 随车名称 = '贴息' ) t10 ON t10.`报价编号` = e.`报价编号` \n" +
                "LEFT JOIN ( SELECT 报价编号, 随车成本 成本 FROM `报价随车表` WHERE 随车类型 = '厂家' AND 随车名称 = '贴息' ) t11 ON t11.`报价编号` = e.`报价编号` LEFT JOIN `库存利润表` v \n" +
                "ON b.`库存编号` = v.`库存编号` LEFT JOIN `LSH经销商表` j ON j.`许可_BMBS` = b.`所属经销商` LEFT JOIN `LSH经销商表` x ON x.`许可_BMBS` = b.`采购批发商` LEFT JOIN `LSH库存内部可见` AS w \n" +
                "ON w.`库存编号` = b.`库存编号` AND ( w.`内部可见角色` = 'SC/CDO可见' OR w.`内部可见角色` = '' ) LEFT JOIN `用户` y ON y.`许可` = b.`所属经销商` AND y.`用户名` = e.`销售顾问` \n" +
                "WHERE b.所属经销商= :dealer OR (b.调入经销商= :dealer and b.调车物流状态 is not null and b.调车物流状态 <>'') or (b.调出经销商=:dealer and b.调车物流状态='调出出库') )aa\n" +
                "where 1=1 ";
        Map params = Maps.newHashMap();
        params.put("dealer", dealer);
        Iterator<Map.Entry<String, String>> it = map.entrySet().iterator();
        while (it.hasNext()) {
            Map.Entry<String, String> entry = it.next();
            //过滤掉前端传回的参数map中 "1":"1" 的情况
            if ("1".equals(entry.getKey())) {
                continue;
            }
            if ((!"订单签署日期1".equals(entry.getKey()) && !"订单签署日期2".equals(entry.getKey())) &&
                    (!"交车日期1".equals(entry.getKey()) && !"交车日期2".equals(entry.getKey())) &&
                    (!"开票日期1".equals(entry.getKey()) && !"开票日期2".equals(entry.getKey()))) {
                sql += " AND aa.`" + entry.getKey() + "` like '%" + entry.getValue() + "%' ";
            } else if ("订单签署日期1".equals(entry.getKey())) {
                sql += " AND aa.`订单签署日期` >='" + entry.getValue() + "' ";
            } else if ("订单签署日期2".equals(entry.getKey())) {
                sql += " AND aa.`订单签署日期` <='" + entry.getValue() + "' ";
            } else if ("交车日期1".equals(entry.getKey())) {
                sql += " AND aa.`交车日期` >='" + entry.getValue() + "' ";
            } else if ("交车日期2".equals(entry.getKey())) {
                sql += " AND aa.`交车日期` <='" + entry.getValue() + "' ";
            } else if ("开票日期1".equals(entry.getKey())) {
                sql += " AND aa.`开票日期` >='" + entry.getValue() + "' ";
            } else if ("开票日期2".equals(entry.getKey())) {
                sql += " AND aa.`开票日期` <='" + entry.getValue() + "' ";
            }
        }
        sql += " ORDER BY aa.`开票日期` DESC, aa.`订单审批通过日期` DESC, aa.`批发日期`DESC, aa.`生产日期` DESC, aa.`生产号` DESC";

        return this.getPage(sql, params, request.getPageSize(), request.getPageIndex());
    }

    @Override
    public PageData getOtherDealerInfo(String dealer, Map map, RequestBase request) {
        String sql = "SELECT * FROM (SELECT a.`品牌` `品牌`, a.`来源`, a.`级别` 车系, a.`车型`, a.`车型详细` 车款, b.`生产日期`, IF ( b.`生产号` \n" +
                "LIKE '%IRISTechNV_%' OR b.`生产号` LIKE '%IRISTechnologies%', '', b.`生产号` ) `生产号`, \n" +
                "IF ( b.`车架号` LIKE '%IRISTechNV_%' OR b.`车架号` LIKE '%IRISTechnologies%', '', b.`车架号` ) `车架号（VIN）`,\n" +
                " b.`欧版底盘号` `欧版底盘号（FIN)`, b.`发动机号`, b.`VSB号`, b.`颜色代码`, b.`颜色`, b.`内饰代码`, b.`内饰`, b.`原厂配置`, \n" +
                "b.`加配配置`, b.`加配价格含税` `加配价格（含税）`, b.`采购价格含税` `采购价格（含税）`, b.`采购价格未税` `采购价格（不含税）`, \n" +
                "a.`MSRP` `BI车款标配MSRP（含税）`, b.`标配MSRP含税` `标配MSRP（含税）`, b.`实际MSRP含税` `实际MSRP（含税）`, b.`SNP含税` `标配区域限价-SNP（含税）`, \n" +
                "b.`厂家确认订单日期`, b.`预计到港日期` `预计到港日期（ETA）`, b.`实际到港日期` `实际到港日期（ATA）`, b.`预计下线日期`, b.`港口代码`, b.`港口名称`, \n" +
                "b.`车辆物流状态`, b.`车辆零售状态`, b.`车辆零售类型`, b.`HOLD日期`, b.`申请释放日期`, b.`批发日期`, b.`批发付款到期日`, b.`NBI车辆状态`, b.`预计到店日期`, \n" +
                "b.`店内车辆位置`, b.`二次PDI完成日期`, b.`二次PDI结果`, b.`入库日期`, b.`出库日期`, b.`库龄天数`, b.`建议三次PDI日期`, b.`三次PDI完成日期` `三次PDI 完成日期`,\n" +
                " b.`三次PDI结果` `三次PDI 结果`, b.`车辆受损情况`, b.`商检单到店状态`, b.`关单到店状态`, b.`合格证到店状态`, b.`合格证号`, b.`商检单号`, b.`关单号`, b.`批发商`,\n" +
                " x.`经销商名` `采购经销商`, j.`经销商名` `所属经销商`, b.`外部可见级别`, w.`内部可见角色` `内部可见级别`, f.`上牌类型`, h.`订单客户姓名` `订单客户`, f.`邮箱` `顾客email`, \n" +
                "IF ( f.`身份证类型` = '身份证', f.`身份证`, '' ) `顾客身份证号码`, IF ( f.`身份证类型` = '外籍ID', f.`身份证`, '' ) `顾客外籍ID号码`, \n" +
                "IF ( f.`身份证类型` = '组织结构', f.`身份证`, '' ) `组织机构代码`, g.`首次上牌日期` `置换车辆上牌年月`, IF ( e.`订单编号` IS NULL, '', \n" +
                "IF ( e.`金融` IS NULL, IFNULL(e.`折后价`, 0), IFNULL(l.`首付额`, 0) + IFNULL(l.`金融服务费`, 0) + IFNULL(l.`保证金金额`, 0)) + IFNULL(q.`保险小计`, 0) + \n" +
                "IFNULL(m.`精品折后价`, 0) + IFNULL(n.`延保折后价`, 0) + IFNULL(o.`费用折后价`, 0) - IFNULL(g.`置换估价`, 0) - IFNULL(t1.`成本`, 0) - IFNULL(t9.`成本`, 0) - \n" +
                "IFNULL(t10.`成本`, 0) - IFNULL(t11.`成本`, 0)) `车辆落地价（不含购置税）`, IFNULL(e.`原价`, 0) - IFNULL(e.`折后价`, 0) `车辆现金折扣`, e.`折后价` `车辆销售价`, \n" +
                "g.`置换估价`, n.`延保原价` `五星延保MSRP（含税）`, n.`延保折后价` `五星延保实收金额（含税）`, n.`延保原价` `五星延保成本（含税）`, h.`订单流水号` `Case号`, h.`订单号`, \n" +
                "IF ( h.`订金类型` = '意向金', h.`订金金额`, '' ) `意向金金额`, \n" +
                "IF ( h.`订金类型` = '订金', h.`订金金额`, '' ) `订金金额`, h.`无息退款天数` `意向金无息退款天数`, h.`资源释放期限` `释放资源期限`, h.`申请修改原因`, h.`撤销订单原因`, \n" +
                "IF ( e.`金融` IS NULL, e.`折后价`, l.`首付额` + l.`保证金金额` ) - h.`订金金额` `车辆尾款（顾客）`, \n" +
                "' ' `代付款`, r.`应付金额` `应收金额`, r.`实付金额` `实收金额`, d.`销售价格`, d.`不含税价`, \n" +
                "h.`订单审批通过日期`, h.`订单签署日期`, h.`订金交付日期`, h.`订单失销日期`, h.`退订订金金额` `退订金金额`, h.`退订金转账日期`, h.`订单修改日期`, \n" +
                "h.`订金加减金额` `订金+/-金额`, h.`订金加减日期` `订金+/-日期`, d.`合同签约日期` `合同签署日期`, d.`开票时间` `开票日期`, d.`合同金额`, d.`余款金额`,\n" +
                " h.`预计开票日期` `合同交货日期`, d.`交车日期`, d.`上牌省` `上牌省份`, d.`上牌市` `上牌城市`, d.`上牌区` `上牌行政区`, d.`上牌地址` `上牌具体地址`, \n" +
                "d.`上牌日期`, d.`牌照号`, IF ( e.`置换编号` IS NULL, '', '是' ) `是否置换`, g.`置换类型` `店内置换/店外置换`, g.`置换估价` `二手车价格`, d.`开票客户姓名` `车主/开票人名称`, \n" +
                "IF ( d.`开票客户身份证类型` = '身份证', d.`开票客户身份证`, '' ) `车主/开票人身份证号码`, IF ( d.`开票客户身份证类型` = '外籍ID', d.`开票客户身份证`, '' ) `外籍车主车主/开票人ID号码`, \n" +
                "d.`开票客户机构代码` `车主/开票人组织机构代码`, d.`开票客户电话` `车主/开票人电话号码`, d.`提车代理人`, d.`提车代理人身份证`, ' ' `外籍提车代理人ID号码`, \n" +
                "IF ( d.`交车日期` IS NULL, '', d.`售后联系人` ) `售后联系人1`, IF ( d.`交车日期` IS NULL, '', d.`售后联系人关系` ) `售后联系人1身份`, \n" +
                "IF ( d.`交车日期` IS NULL, '', d.`售后联系人电话` ) `售后联系人1电话号码`, IF ( d.`交车日期` IS NULL, '', d.`售后联系人地址` ) `售后联系人1联系地址`, \n" +
                "IF ( d.`交车日期` IS NULL, '', d.`售后联系人微信` ) `售后联系人1微信ID`, v.`上报厂家`, v.`上报厂家日期`, v.`上报SIMS`, v.`上报SIMS日期`, ' ' `GP1`, \n" +
                "' ' `GP1%`, s.`经销商随车成本`, t1.`成本` `厂家SI_保险成本`, t2.`成本` `厂家SI_购置税成本`, t3.`成本` `厂家SI_SSSC成本`, t4.`成本` `厂家SI_旅游礼包成本`, \n" +
                "t5.`成本` `厂家SI_SD卡成本`, t6.`成本` `厂家SI_加油卡成本`, t7.`成本` `厂家SI_实物礼品成本`, t8.`成本` `厂家SI_服务礼券成本`, ' ' `GP2`, \n" +
                "' ' `GP2%`, t1.`实收` `厂家SI_保险实收`, t2.`实收` `厂家SI_购置税实收`, t3.`实收` `厂家SI_SSSC实收`, t4.`实收` `厂家SI_旅游礼包实收`, \n" +
                "t5.`实收` `厂家SI_SD卡实收`, t6.`实收` `厂家SI_加油卡实收`, t7.`实收` `厂家SI_实物礼品实收`, t8.`实收` `厂家SI_服务礼券实收`, v.`零售SI` `厂家零售SI`, \n" +
                "v.`批发SI` `厂家批售SI`, ' ' `GP3`, ' ' `GP3%`, p.`上牌服务费`, p.`其他服务费`, n.`延保折后价` `现金销售五星延保毛利`, c.`折后价` - c.`成本` `现金销售后市场产品毛利`, \n" +
                "v.`后市场产品毛利` `后市场毛利`, l.`金融手续费`, u.`交强险手续费` + u.`商业险手续费` `新保手续费`, g.`置换评估费` `置换手续费`, ' ' `Sales T.GP`, ' ' `Sales T.GP%`, \n" +
                "y.`人员姓名` `销售顾问`, b.`库存编号` FROM `LSH库存表` b LEFT JOIN `报价单表` e ON e.`报价编号` = b.`报价编号` LEFT JOIN `LSH车型` a ON b.`车型编号` = a.`车型编号` \n" +
                "LEFT JOIN `报价交车表` d ON e.`交车` = d.`编号` LEFT JOIN `报价客户表` f ON e.`客户详细` = f.`编号` LEFT JOIN `报价置换表` g ON e.`置换编号` = g.`置换编号` \n" +
                "LEFT JOIN `报价订单表` h ON e.`订单编号` = h.`订单编号` LEFT JOIN `报价金融表` l ON e.`金融` = l.`金融编号` LEFT JOIN `报价精品表` m ON e.`精品` = m.`编号` \n" +
                "LEFT JOIN ( SELECT 编号, SUM(折后价) 折后价, SUM(成本) 成本 FROM `报价精品详细表` GROUP BY 编号 ) c ON e.`精品` = c.`编号` LEFT JOIN `报价五星延保` n ON e.`延保编号` = n.`延保编号` \n" +
                "LEFT JOIN `报价费用表` o ON e.`费用编号` = o.`费用编号` LEFT JOIN `报价保险表` u ON e.`保险编号` = u.`保险编号` AND u.`投保类型` = '新保' \n" +
                "LEFT JOIN ( SELECT 费用详细编号, 费用编号, SUBSTRING_INDEX( GROUP_CONCAT(费用原价), ',', 1 ) 上牌服务费, SUBSTRING_INDEX( GROUP_CONCAT(费用原价), ',' ,- 1 ) 其他服务费 \n" +
                "FROM 报价费用详细表 WHERE 费用名称 = '其他服务费' OR 费用名称 = '上牌服务费' GROUP BY 费用编号 ) p ON e.`费用编号` = p.`费用编号` LEFT JOIN ( \n" +
                "SELECT 保险编号, SUM(折后价) 保险小计 FROM `报价保险详细表` WHERE `险种` <> '车船税' GROUP BY 保险编号 ) q ON e.`保险编号` = q.`保险编号` LEFT JOIN `报价付款详细` r \n" +
                "ON e.`报价编号` = r.`报价编号` AND r.`付款事由` = '总计' LEFT JOIN ( SELECT 报价编号, SUM(随车成本) 经销商随车成本 FROM `报价随车表` WHERE 随车类型 = '经销商' \n" +
                "GROUP BY 报价编号 ) s ON s.`报价编号` = e.`报价编号` LEFT JOIN ( SELECT 报价编号, 随车成本 成本, 厂家返利 实收 FROM `报价随车表` WHERE 随车类型 = '厂家' \n" +
                "AND 随车名称 = '保险' ) t1 ON t1.`报价编号` = e.`报价编号` LEFT JOIN ( SELECT 报价编号, 随车成本 成本, 厂家返利 实收 FROM `报价随车表` WHERE 随车类型 = '厂家' \n" +
                "AND 随车名称 = '购置税' ) t2 ON t2.`报价编号` = e.`报价编号` LEFT JOIN ( SELECT 报价编号, 随车成本 成本, 厂家返利 实收 FROM `报价随车表` WHERE 随车类型 = '厂家' \n" +
                "AND 随车名称 = 'SSSC' ) t3 ON t3.`报价编号` = e.`报价编号` LEFT JOIN ( SELECT 报价编号, 随车成本 成本, 厂家返利 实收 FROM `报价随车表` WHERE 随车类型 = '厂家' \n" +
                "AND 随车名称 = '旅游礼包' ) t4 ON t4.`报价编号` = e.`报价编号` LEFT JOIN ( SELECT 报价编号, 随车成本 成本, 厂家返利 实收 FROM `报价随车表` WHERE 随车类型 = '厂家' \n" +
                "AND 随车名称 = 'SD卡' ) t5 ON t5.`报价编号` = e.`报价编号` LEFT JOIN ( SELECT 报价编号, 随车成本 成本, 厂家返利 实收 FROM `报价随车表` WHERE 随车类型 = '厂家' \n" +
                "AND 随车名称 = '加油卡' ) t6 ON t6.`报价编号` = e.`报价编号` LEFT JOIN ( SELECT 报价编号, 随车成本 成本, 厂家返利 实收 FROM `报价随车表` WHERE 随车类型 = '厂家' \n" +
                "AND 随车名称 = '实物礼品' ) t7 ON t7.`报价编号` = e.`报价编号` LEFT JOIN ( SELECT 报价编号, 随车成本 成本, 厂家返利 实收 FROM `报价随车表` WHERE 随车类型 = '厂家' \n" +
                "AND 随车名称 = '服务礼券' ) t8 ON t8.`报价编号` = e.`报价编号` LEFT JOIN ( SELECT 报价编号, 随车成本 成本 FROM `报价随车表` WHERE 随车类型 = '经销商' AND 随车名称 = '保险' ) t9 \n" +
                "ON t9.`报价编号` = e.`报价编号` LEFT JOIN ( SELECT 报价编号, 随车成本 成本 FROM `报价随车表` WHERE 随车类型 = '经销商' AND 随车名称 = '贴息' ) t10 ON t10.`报价编号` = e.`报价编号` \n" +
                "LEFT JOIN ( SELECT 报价编号, 随车成本 成本 FROM `报价随车表` WHERE 随车类型 = '厂家' AND 随车名称 = '贴息' ) t11 ON t11.`报价编号` = e.`报价编号` LEFT JOIN `库存利润表` v \n" +
                "ON b.`库存编号` = v.`库存编号` LEFT JOIN `LSH经销商表` j ON j.`许可_BMBS` = b.`所属经销商` LEFT JOIN `LSH经销商表` x ON x.`许可_BMBS` = b.`采购批发商` LEFT JOIN `LSH库存内部可见` AS w \n" +
                "ON w.`库存编号` = b.`库存编号` AND ( w.`内部可见角色` = 'SC/CDO可见' OR w.`内部可见角色` = '' ) LEFT JOIN `用户` y ON y.`许可` = b.`所属经销商` AND y.`用户名` = e.`销售顾问` \n" +
                "WHERE b.所属经销商= :dealer AND b.车辆零售状态 = '未卖')aa\n" +
                "where 1=1 ";
        Map params = Maps.newHashMap();
        params.put("dealer", dealer);
        Iterator<Map.Entry<String, String>> it = map.entrySet().iterator();
        while (it.hasNext()) {
            Map.Entry<String, String> entry = it.next();
            //过滤掉前端传回的参数map中 "1":"1" 的情况
            if ("1".equals(entry.getKey())) {
                continue;
            }
            sql += " AND aa." + entry.getKey() + " like '%" + entry.getValue() + "%' ";
        }
        sql += " ORDER BY aa.`开票日期` DESC, aa.`订单审批通过日期` DESC, aa.`批发日期`DESC, aa.`生产日期` DESC, aa.`生产号` DESC";

        return this.getPage(sql, params, request.getPageSize(), request.getPageIndex());
    }

    @Override
    public PageData getOtherStoreCar(SalesService.GetOtherStoreCarReq req) {
        String sql = "SELECT 	a.`品牌` `品牌`, 	a.`来源`, 	a.`级别` 车系, 	a.`车型`, 	a.`车型详细` 车款, 	b.`生产日期`, 	"
                + "if (b.`生产号` LIKE '%IRISTechNV_%' OR b.`生产号` LIKE '%IRISTechnologies%', '',b.`生产号`) `生产号`, 	"
                + "if (b.`车架号` LIKE '%IRISTechNV_%' OR b.`车架号` LIKE '%IRISTechnologies%', '',b.`车架号`) `车架号（VIN）`, 	b.`欧版底盘号` `欧版底盘号（FIN)`, 	b.`发动机号`, 	b.`VSB号`, 	b.`颜色代码`, 	b.`颜色`, 	b.`内饰代码`, 	b.`内饰`, 	b.`原厂配置`, 	b.`加配配置`, 	b.`加配价格含税` `加配价格（含税）`, 	b.`采购价格含税` `采购价格（含税）`, 	b.`采购价格未税` `采购价格（不含税）`, 	b.`BI车款标配MSRP含税` `BI车款标配MSRP（含税）`, 	b.`标配MSRP含税` `标配MSRP（含税）`, 	b.`实际MSRP含税` `实际MSRP（含税）`, 	b.`SNP含税` `标配区域限价-SNP（含税）`, 	b.`厂家确认订单日期`, 	b.`预计到港日期` `预计到港日期（ETA）`, 	b.`实际到港日期` `实际到港日期（ATA）`, 	b.`预计下线日期`, 	b.`港口代码`, 	b.`港口名称`, 	b.`车辆物流状态`, 	b.`车辆零售状态`, 	b.`车辆零售类型`, 	b.`HOLD日期`, 	b.`申请释放日期`, 	b.`批发日期`, 	b.`批发付款到期日`, 	b.`NBI车辆状态`, 	b.`预计到店日期`, 	b.`店内车辆位置`, 	b.`二次PDI完成日期`, 	b.`二次PDI结果`, 	b.`入库日期`, 	b.`出库日期`, 	b.`库龄天数`, 	b.`建议三次PDI日期`, 	b.`三次PDI完成日期` `三次PDI 完成日期`, 	b.`三次PDI结果` `三次PDI 结果`, 	b.`车辆受损情况`, 	b.`商检单到店状态`, 	b.`关单到店状态`, 	b.`合格证到店状态`, 	b.`合格证号`, 	b.`商检单号`, 	b.`关单号`, 	b.`批发商`, 	x.`经销商名` 采购经销商, 	j.`经销商名` `所属经销商`, 	b.`外部可见级别`, 	w.`内部可见角色` `内部可见级别`, 	f.`上牌类型`, 	f.`邮箱` `顾客email`,  "
                + "IF ( 	f.`身份证类型` = '身份证', 	f.`身份证`, 	'' ) `顾客身份证号码`,  "
                + "IF ( 	f.`身份证类型` = '外籍ID', 	f.`身份证`, 	'' ) `顾客外籍ID号码`,  "
                + "IF ( 	f.`身份证类型` = '组织结构', 	f.`身份证`, 	'' ) `组织机构代码`,  g.`首次上牌日期` `置换车辆上牌年月`,  "
                + "IF ( 	e.`金融` IS NULL, 	e.`折后价`, 	l.`首付额` ) + q.`保险小计` + m.`精品折后价` + n.`延保折后价` + o.`费用折后价` - g.`置换估价` `车辆落地价`,  e.`原价` - e.`折后价` `车辆现金折扣`,  a.`MSRP` - (e.`原价` - e.`折后价`) `车辆销售价`,  g.`置换估价`,  n.`延保原价` `五星延保MSRP（含税）`,  n.`延保折后价` `五星延保实收金额（含税）`,  n.`延保原价` `五星延保成本（含税）`,  h.`订单流水号` `Case号`,  h.`订单号`,  "
                + "IF ( 	h.`订金类型` = '意向金', 	h.`订金金额`, 	'' ) `意向金金额`,  "
                + "IF ( 	h.`订金类型` = '订金', 	h.`订金金额`, 	'' ) `订金金额`,  h.`无息退款天数` `意向金无息退款天数`,  h.`资源释放期限` `释放资源期限`,  h.`申请修改原因`,  h.`撤销订单原因`,  "
                + "IF ( 	e.`金融` IS NULL, 	e.`折后价`, 	l.`首付额` + l.`保证金金额` ) - h.`订金金额` `车辆尾款（顾客）`,  '' `代付款`,  r.`应付金额` `应收金额`,  r.`实付金额` `实收金额`,  d.`销售价格`,  d.`不含税价`,  h.`订单审批通过日期`,  h.`订单签署日期`,  h.`订金交付日期`,  h.`订单失销日期`,  h.`退订订金金额` `退订金金额`,  h.`退订金转账日期`,  h.`订单修改日期`,  h.`订金加减金额` `订金+/-金额`,  h.`订金加减日期` `订金+/-日期`,  d.`合同签约日期` `合同签署日期`,  d.`开票时间` `开票日期`,  d.`合同金额`,  d.`余款金额`,  h.`预计开票日期` `合同交货日期`,  d.`交车日期`,  d.`上牌省` `上牌省份`,  d.`上牌市` `上牌城市`,  d.`上牌区` `上牌行政区`,  d.`上牌地址` `上牌具体地址`,  d.`上牌日期`,  d.`牌照号`,  "
                + "IF ( 	e.`置换编号` = '', 	'否', 	'是' ) `是否置换`,  g.`置换类型` `店内置换/店外置换`,  g.`二手车价格`,  d.`开票客户姓名` `车主/开票人名称`, "
                + "IF ( 	d.`开票客户身份证类型` = '身份证', 	d.`开票客户身份证`, 	'' ) `车主/开票人身份证号码`,  "
                + "IF ( 	d.`开票客户身份证类型` = '外籍ID', 	d.`开票客户身份证`, 	'' ) `外籍车主车主/开票人ID号码`,  d.`开票客户机构代码` `车主/开票人组织机构代码`,  d.`开票客户电话` `车主/开票人电话号码`,  d.`提车代理人`,  d.`提车代理人身份证`,  '' `外籍提车代理人ID号码`,  d.`售后联系人` `售后联系人1`,  d.`售后联系人关系` `售后联系人1身份`,  d.`售后联系人电话` `售后联系人1电话号码`,  d.`售后联系人地址` `售后联系人1联系地址`,  d.`售后联系人微信` `售后联系人1微信ID`,  v.`上报厂家`,  v.`上报厂家日期`,  v.`上报SIMS`,  v.`上报SIMS日期`,  '' `GP1`,  '' `GP1%`,  s.`经销商随车成本`,  t1.`成本` `厂家SI_保险成本`,  t2.`成本` `厂家SI_购置税成本`,  t3.`成本` `厂家SI_SSSC成本`,  t4.`成本` `厂家SI_旅游礼包成本`,  t5.`成本` `厂家SI_SD卡成本`,  t6.`成本` `厂家SI_加油卡成本`,  t7.`成本` `厂家SI_实物礼品成本`,  t8.`成本` `厂家SI_服务礼券成本`,  '' `GP2`,  '' `GP2%`,  t1.`实收` `厂家SI_保险实收`,  t2.`实收` `厂家SI_购置税实收`,  t3.`实收` `厂家SI_SSSC实收`,  t4.`实收` `厂家SI_旅游礼包实收`,  t5.`实收` `厂家SI_SD卡实收`,  t6.`实收` `厂家SI_加油卡实收`,  t7.`实收` `厂家SI_实物礼品实收`,  t8.`实收` `厂家SI_服务礼券实收`, v.`零售SI` `厂家零售SI`,v.`批发SI` `厂家批售SI`, '' `GP3`,  '' `GP3%`,  p.`上牌服务费`,  p.`其他服务费`,  n.`延保原价` - n.`延保折后价` `现金销售五星延保毛利`,  m.`精品原价` - m.`精品折后价` `现金销售后市场产品毛利`,  '' `后市场毛利`,  l.`金融手续费`,  u.`交强险手续费` + u.`商业险手续费` `新保手续费`,  g.`置换评估费` `置换手续费`,  '' `Sales T.GP`,  '' `Sales T.GP%`,  b.`库存编号` FROM 	`LSH库存表` b "
                + "LEFT JOIN `报价单表` e ON e.`报价编号` = b.`报价编号` " + "LEFT JOIN `LSH车型` a ON b.`车型编号` = a.`车型编号` "
                + "LEFT JOIN `报价交车表` d ON e.`交车` = d.`编号` " + "LEFT JOIN `报价客户表` f ON e.`客户详细` = f.`编号` "
                + "LEFT JOIN `报价置换表` g ON e.`置换编号` = g.`置换编号` " + "LEFT JOIN `报价订单表` h ON e.`订单编号` = h.`订单编号` "
                + "LEFT JOIN `报价金融表` l ON e.`金融` = l.`金融编号` " + "LEFT JOIN `报价精品表` m ON e.`精品` = m.`编号` "
                + "LEFT JOIN `报价五星延保` n ON e.`延保编号` = n.`延保编号` " + "LEFT JOIN `报价费用表` o ON e.`费用编号` = o.`费用编号` "
                + "LEFT JOIN `报价保险表` u ON e.`保险编号` = u.`保险编号` AND u.`投保类型` = '新保' "
                + "LEFT JOIN ( 	SELECT 		费用详细编号, 		费用编号, 		SUBSTRING_INDEX( 			GROUP_CONCAT(费用原价), 			',', 			1 		) 上牌服务费, 		SUBSTRING_INDEX( 			GROUP_CONCAT(费用原价), 			',' ,- 1 		) 其他服务费 	FROM 		报价费用详细表 	WHERE 		费用名称 = '其他服务费' 	OR 费用名称 = '上牌服务费' 	GROUP BY 		费用编号 ) p ON e.`费用编号` = p.`费用编号` "
                + "LEFT JOIN ( 	SELECT 		保险编号, 		SUM(折后价) 保险小计 	FROM 		`报价保险详细表` 	WHERE 		`险种` <> '交强险' 	OR `险种` <> '车船税' 	GROUP BY 		保险编号 ) q ON e.`保险编号` = q.`保险编号` "
                + "LEFT JOIN `报价付款详细` r ON e.`报价编号` = r.`报价编号` AND r.`付款事由` = '总计' "
                + "LEFT JOIN ( 	SELECT 		报价编号, 		SUM(随车成本) 经销商随车成本 	FROM 		`报价随车表` 	WHERE 		随车类型 = '经销商' 	GROUP BY 		报价编号 ) s ON s.`报价编号` = e.`报价编号` "
                + "LEFT JOIN ( 	SELECT 		报价编号, 		随车成本 成本, 		厂家返利 实收 	FROM 		`报价随车表` 	WHERE 		随车类型 = '厂家' 	AND 随车名称 = '保险' ) t1 ON t1.`报价编号` = e.`报价编号` "
                + "LEFT JOIN ( 	SELECT 		报价编号, 		随车成本 成本, 		厂家返利 实收 	FROM 		`报价随车表` 	WHERE 		随车类型 = '厂家' 	AND 随车名称 = '购置税' ) t2 ON t2.`报价编号` = e.`报价编号` "
                + "LEFT JOIN ( 	SELECT 		报价编号, 		随车成本 成本, 		厂家返利 实收 	FROM 		`报价随车表` 	WHERE 		随车类型 = '厂家' 	AND 随车名称 = 'SSSC' ) t3 ON t3.`报价编号` = e.`报价编号` "
                + "LEFT JOIN ( 	SELECT 		报价编号, 		随车成本 成本, 		厂家返利 实收 	FROM 		`报价随车表` 	WHERE 		随车类型 = '厂家' 	AND 随车名称 = '旅游礼包' ) t4 ON t4.`报价编号` = e.`报价编号` "
                + "LEFT JOIN ( 	SELECT 		报价编号, 		随车成本 成本, 		厂家返利 实收 	FROM 		`报价随车表` 	WHERE 		随车类型 = '厂家' 	AND 随车名称 = 'SD卡' ) t5 ON t5.`报价编号` = e.`报价编号` "
                + "LEFT JOIN ( 	SELECT 		报价编号, 		随车成本 成本, 		厂家返利 实收 	FROM 		`报价随车表` 	WHERE 		随车类型 = '厂家' 	AND 随车名称 = '加油卡' ) t6 ON t6.`报价编号` = e.`报价编号` "
                + "LEFT JOIN ( 	SELECT 		报价编号, 		随车成本 成本, 		厂家返利 实收 	FROM 		`报价随车表` 	WHERE 		随车类型 = '厂家' 	AND 随车名称 = '实物礼品' ) t7 ON t7.`报价编号` = e.`报价编号` "
                + "LEFT JOIN ( 	SELECT 		报价编号, 		随车成本 成本, 		厂家返利 实收 	FROM 		`报价随车表` 	WHERE 		随车类型 = '厂家' 	AND 随车名称 = '服务礼券' ) t8 ON t8.`报价编号` = e.`报价编号` "
                + "LEFT JOIN `库存利润表` v ON b.`库存编号` = v.`库存编号` " + "LEFT JOIN `LSH经销商表` j ON j.`许可_BMBS` = b.`所属经销商` "
                + "LEFT JOIN `LSH经销商表` x ON x.`许可_BMBS` = b.`采购批发商` "
                + "LEFT JOIN `LSH库存内部可见` AS w ON w.`库存编号` = b.`库存编号` AND ( w.`内部可见角色` = 'SC/CDO可见' OR w.`内部可见角色` = '' ) "
                + "WHERE 	1=1  ";
        if (req != null && !Strings.isNullOrEmpty(req.area1) && !Strings.isNullOrEmpty(req.area2)
                && !Strings.isNullOrEmpty(req.dealer) && !Strings.isNullOrEmpty(req.CTA) && !verifyDealerArea(req)) {
            sql += "AND `外部可见级别` = '全国可见' AND j.大区 = :area1 AND j.小区 = :area2 AND j.`经销商名` = :dealer AND j.CTA = :CTA";
        } else {
            sql += "AND ( `外部可见级别` = '全国可见' ";
            if (!Strings.isNullOrEmpty(req.area1)) {
                sql += "OR `外部可见级别` = '大区可见' ";
            }
            if (!Strings.isNullOrEmpty(req.area2)) {
                sql += "OR `外部可见级别` = '小区可见' ";
            }
            if (!Strings.isNullOrEmpty(req.CTA)) {
                sql += "OR `外部可见级别` = 'CTA可见' ) ";
            }
            sql += "AND j.`经销商名` = '%s' ";
            sql += "AND j.大区 = '%s' ";
            sql += "AND j.小区 = '%s' ";
        }
        Map params = Maps.newHashMap();
        params.put("area1", req.area1);
        params.put("area2", req.area2);
        params.put("dealer", req.dealer);
        params.put("CTA", req.CTA);
        return this.getPage(sql, params, req.getPageSize(), req.getPageIndex());
    }

    boolean verifyDealerArea(SalesService.GetOtherStoreCarReq req) {
        String sql = "select 经销商 from LSH经销商表 where 大区 =:area1 AND 小区 =:area2 AND 经销商名 = :dealer";
        Map params = Maps.newHashMap();
        params.put("area1", req.area1);
        params.put("area2", req.area2);
        params.put("dealer", req.dealer);
        List list = this.getList(sql, params);
        return list.size() > 0 ? true : false;

    }

    @Override
    public List SelectCarTransferLog(String VIN, String type) {
        String sql = "";
        if ("user".equalsIgnoreCase(type)) {
            sql = "SELECT internal_apply_user `调入内部申请`, internal_approval_user `调入内部审批`, external_apply_user `调入外部申请`, out_confirm_user `调出确认`, invoice_issuing_user `调车发票开出`, fund_out_user `调车款项付出`, fund_in_user `调车款项到账`, stock_out_user `车辆出库`, stock_in_user `车辆入库` FROM car_transfer_log WHERE VIN = :VIN";
        } else if ("time".equalsIgnoreCase(type)) {
            sql = "SELECT internal_apply_date `调入内部申请`, internal_approval_date `调入内部审批`, external_apply_date `调入外部申请`, out_confirm_date `调出确认`, invoice_issuing_date `调车发票开出`, fund_out_date `调车款项付出`, fund_in_date `调车款项到账`, stock_out_date `车辆出库`, stock_in_date `车辆入库` FROM car_transfer_log WHERE VIN = :VIN";
        } else {
            sql = "SELECT a.`预计交车日期` from `报价单表` AS a JOIN `LSH库存表` AS b ON a.`库存编号` = b.`库存编号` WHERE b.`车架号` = :VIN";
        }
        Map params = Maps.newHashMap();
        params.put("VIN", VIN);
        return this.getList(sql, params);
    }

    @Override
    public List getNoReceiptProNo(String permit) {
        String sql = "SELECT a.`生产号` FROM `LSH库存表` as a LEFT JOIN `报价单表` as b ON a.报价编号 = b.`报价编号` LEFT JOIN `报价交车表` as c ON b.`交车`=c.`编号` WHERE a.`所属经销商`= :permit AND `生产号` NOT LIKE 'IRISTechnologies%' AND IFNULL(c.`开票时间`,'') = ''";
        Map params = Maps.newHashMap();
        params.put("permit", permit);
        return this.getList(sql, params);
    }

    @Override
    public List getCompareModelsList() {
        String sql = "SELECT `车型编号`,`品牌`,`来源`,`级别`,`车型`,`车型详细`,`Variant`,`Type`,`Build_date`,`Factory_OP2`,`MSRP`,`退市日期` FROM LSH车型 WHERE `车型详细` <> ''";
        return this.getList(sql);
    }

    /*
    * 原代码 没有 考虑 list为空的情况
    * 11.3/14：34 肖旭东修改
    * */
    @Override
    public boolean getIsShow(String permit) {
        String sql = "select 值 from 销售项目设置 where 许可 = :permit and 设置项目= '导入DMS车辆SC/CDO可见';";
        Map<String, String> params = Maps.newHashMap();
        params.put("permit", permit);
        List list = this.getList(sql, params);
        if (list.size() == 0) {
            return true;
        } else {
            Map a = (Map) list.get(0);
            return !"0".equalsIgnoreCase(a.get("值").toString());
        }
    }

    @Override
    public List getExistProductNo(List<SalesService.SaveLshStockReq> reqList) {
        int i;
        StringBuilder sql = new StringBuilder();
        sql.append("select a.库存编号, a.生产号, a.车架号, d.开票时间, a.调车物流状态, a.出库日期, a.二次PDI完成日期, a.入库日期, a.批发日期  \n" +
                "from LSH库存表 a  \n" +
                "left join 报价单表 b on a.报价编号 = b.报价编号  \n" +
                "left join 报价交车表 d on b.交车 = d.编号  \n" +
                "where 生产号 is not null and (  \n");
        Map<String, String> params = Maps.newHashMap();
        for (i = 0; i < reqList.size(); i++) {
            SalesService.SaveLshStockReq req = reqList.get(i);
            if (i == 0) {
                sql.append("生产号 = :productNo").append(i).append("  \n");
            } else {
                sql.append("or 生产号 = :productNo").append(i).append("  \n");
            }
            params.put("productNo" + i, req.productNo);
        }
        sql.append(")  \n");
        return this.getList(sql.toString(), params);
    }

    @Override
    public List getExistProductNoNew(List<String> reqList) {
        int i;
        StringBuilder sql = new StringBuilder();
        sql.append("select a.库存编号," +
                " a.生产号, " +
                "a.车架号," +
                " d.开票时间, " +
                "a.调车物流状态, " +
                "a.出库日期," +
                " a.二次PDI完成日期," +
                " a.入库日期," +
                " a.批发日期," +
                "a.VSB财务状态 ," +
                "a.`车辆零售状态` ," +
                "a.报价编号 ," +
                "a.所属经销商\n" +
                "from LSH库存表 a  \n" +
                "left join 报价单表 b on a.报价编号 = b.报价编号  \n" +
                "left join 报价交车表 d on b.交车 = d.编号  \n" +
                "where 生产号 is not null and (  \n");
        Map<String, String> params = Maps.newHashMap();
        for (i = 0; i < reqList.size(); i++) {
           String req = reqList.get(i);
            if (i == 0) {
                sql.append("生产号 = :productNo").append(i).append("  \n");
            } else {
                sql.append("or 生产号 = :productNo").append(i).append("  \n");
            }
            params.put("productNo" + i,req);
        }
        sql.append(")  \n");
        return this.getList(sql.toString(), params);
    }

    @Override
   public List getHaveProductNoStock(List<String> reqList){
       int i;
       StringBuilder sql = new StringBuilder();
       sql.append("select a.* \n" +
               "from LSH库存表 a  \n" +
               "where 生产号 is not null and (  \n");
       Map<String, String> params = Maps.newHashMap();
       for (i = 0; i < reqList.size(); i++) {
           String req = reqList.get(i);
           if (i == 0) {
               sql.append("生产号 = :productNo").append(i).append("  \n");
           } else {
               sql.append("or 生产号 = :productNo").append(i).append("  \n");
           }
           params.put("productNo" + i,req);
       }
       sql.append(")  \n");
       return this.getList(sql.toString(), params);
   }

    @Override
    public List getExistVinNew(List<String> reqList) {
        int i;
        StringBuilder sql = new StringBuilder();
        sql.append("select a.库存编号, a.生产号, a.车架号, d.开票时间, a.调车物流状态, a.出库日期, a.二次PDI完成日期, a.入库日期, a.批发日期 ,a.VSB财务状态, a.`车辆零售状态`,a.报价编号  ,a.所属经销商\n" +
                "from LSH库存表 a  \n" +
                "left join 报价单表 b on a.报价编号 = b.报价编号  \n" +
                "left join 报价交车表 d on b.交车 = d.编号  \n" +
                "where 车架号 is not null and (  \n");
        Map<String, String> params = Maps.newHashMap();
        for (i = 0; i < reqList.size(); i++) {
            String req = reqList.get(i);
            if (i == 0) {
                sql.append("车架号 = :vin").append(i).append("  \n");
            } else {
                sql.append("or 车架号 = :vin").append(i).append("  \n");
            }
            params.put("vin" + i, req);
        }
        sql.append(")  \n");
        return this.getList(sql.toString(), params);
    }

    @Override
    public PageData findAllStock(RequestBase req,String permit){
        Map<String, String> params = Maps.newHashMap();
        params.put("permit", permit);
        String sql="SELECT \n" +
                "     a.*,  if (a.`车架号` like '%IRISTechNV_%','',a.车架号 )  车架号new,c.Variant,c.车型详细  " +
                " from lsh库存表   a" +
                " JOIN lsh车型  c  on c.`车型编号`=a.`车型编号`  \n" +
                " left join 报价单表 b on a.报价编号 = b.报价编号   \n" +
                " left join 报价交车表 d on b.交车 = d.编号  \n" +
                " where  a.所属经销商=:permit  and  a.`批发日期`  is  null   and  a.`VSB财务状态` is  NULL" +
                " and d.开票时间 is    null  and a.调车物流状态  is  null  and  a.`车辆零售状态`='未卖' AND `生产号` not LIKE '%IRISTechnologies%'  and a.is_delete <>'1'";
        return this.getPage(sql, params, req.getPageSize(), req.getPageIndex());
    }

    @Override
    public List findAllStock(String permit){
        Map<String, String> params = Maps.newHashMap();
        params.put("permit", permit);
        String sql="SELECT \n" +
                " a.* " +
                " from lsh库存表   a\n" +
                " left join 报价单表 b on a.报价编号 = b.报价编号   \n" +
                " left join 报价交车表 d on b.交车 = d.编号  \n" +
                " where  a.所属经销商=:permit  and  a.`批发日期`  is  null   and  a.`VSB财务状态` is  null" ;
        return this.getList(sql.toString(), params);
    }

    @Override
    public List getExistVin(List<SalesService.SaveLshStockReq> reqList) {
        int i;
        StringBuilder sql = new StringBuilder();
        sql.append("select a.库存编号, a.生产号, a.车架号, d.开票时间, a.调车物流状态, a.出库日期, a.二次PDI完成日期, a.入库日期, a.批发日期  \n" +
                "from LSH库存表 a  \n" +
                "left join 报价单表 b on a.报价编号 = b.报价编号  \n" +
                "left join 报价交车表 d on b.交车 = d.编号  \n" +
                "where 车架号 is not null and (  \n");
        Map<String, String> params = Maps.newHashMap();
        for (i = 0; i < reqList.size(); i++) {
            SalesService.SaveLshStockReq req = reqList.get(i);
            if (i == 0) {
                sql.append("车架号 = :vin").append(i).append("  \n");
            } else {
                sql.append("or 车架号 = :vin").append(i).append("  \n");
            }
            params.put("vin" + i, req.VIN);
        }
        sql.append(")  \n");
        return this.getList(sql.toString(), params);
    }

    /*
    * 原代码 没有 考虑 list为空的情况
    * 11.3/14：34 肖旭东修改
    * */
    @Override
    public boolean getIsBilling(String productNo) {
        String sql = "select d.开票时间 from ( select 交车 ,生产号 from  LSH库存表 a left join 报价单表 b on a.报价编号 = b.报价编号)c  left join 报价交车表 d on c.交车=d.编号 where c.生产号= :productNo";
        Map params = Maps.newHashMap();
        params.put("productNo", productNo);
        List list = this.getList(sql, params);
        boolean flag = false;
        if (list.size() == 0) {
            flag = false;
        } else {
            Map a = (Map) list.get(0);
            if (a.get("开票时间") != null) {
                flag = true;
            } else {
                flag = false;
            }
        }
        return flag;
    }

    // 新增的 方法  15：57  11.10  xxd
    @Override
    public boolean getstatus(String productNo) {
        String sql = "select c.调车物流状态  from ( select 交车 ,生产号, a.`调车物流状态` as  调车物流状态 from  LSH库存表 a  " +
                "  left join 报价单表 b on a.报价编号 = b.报价编号) c  " +
                "  left join 报价交车表 d on c.交车=d.编号  " +
                "  where c.生产号= :productNo  ";
        Map params = Maps.newHashMap();
        params.put("productNo", productNo);
        List list = this.getList(sql, params);
        boolean flag = false;

        // 查不到 数据时  就是 物流状态
        // true不更新

        Map a = (Map) list.get(0);
        if (!"".equals(a.get("调车物流状态")) && a.get("调车物流状态") != null) {
            flag = true;
        } else {
            flag = false;
        }

        return flag;
    }

    @Override
    public Map getLshStockInfo(String productNo) {
        String sql = "select 库存编号, 车架号,出库日期,二次PDI完成日期,入库日期,批发日期 from LSH库存表  where 生产号 = :productNo";
        Map params = Maps.newHashMap();
        params.put("productNo", productNo);
        List list = this.getList(sql, params);
        return list.size() > 0 ? (Map) list.get(0) : null;
    }

//    @Override
//    public boolean checkExistByStockCode(Integer stock_code) {
//        String sql = "select id from car_transfer_log where stock_code = :stock_code";
//        Map params = Maps.newHashMap();
//        params.put("stock_code", stock_code);
//        List list = this.getList(sql, params);
//        return list.size() > 0 ? true : false;
//    }

    @Override
    public Map getTransferCustInfo(Integer stock_code) {
        String sql = "SELECT custom_name,custom_tel,expectedLateInStockDate,expectedLateMoneyInAccount FROM car_transfer_log WHERE stock_code = :stock_code";
        Map params = Maps.newHashMap();
        params.put("stock_code", stock_code);
        List list = this.getList(sql, params);
        return list.size() > 0 ? (Map) list.get(0) : null;
    }

    @Override
    public boolean selectExistNum(String productNo) {
        String sql = "select 库存编号 from LSH库存表  where 生产号 = :productNo";
        Map params = Maps.newHashMap();
        params.put("productNo", productNo);
        List list = this.getList(sql, params);
        return list.size() > 0 ? true : false;
    }

    @Override
    public List getCarTransferCustInfo(String dlr, String prodCode) {
        String sql = "SELECT   \n" +
                "  id,  \n" +
                "  internal_apply_user,  \n" +
                "  internal_apply_date,  \n" +
                "  internal_approval_user,   \n" +
                "  internal_approval_date,  \n" +
                "  external_apply_user,   \n" +
                "  external_apply_date,  \n" +
                "  out_confirm_user,   \n" +
                "  out_confirm_date,  \n" +
                "  invoice_issuing_user,   \n" +
                "  invoice_issuing_date,  \n" +
                "  fund_out_user,   \n" +
                "  fund_out_date,  \n" +
                "  fund_in_user,   \n" +
                "  fund_in_date,  \n" +
                "  stock_out_user,   \n" +
                "  stock_out_date,  \n" +
                "  stock_in_user,  \n" +
                "  stock_in_date,  \n" +
                "  is_emegency  \n" +
                "FROM car_transfer_log a  \n" +
                "LEFT JOIN `LSH库存表` b ON b.`库存编号` = a.stock_code   \n" +
                "WHERE b.生产号 = :prodCode  \n" +
                "and (in_dealer = :dlr or out_dealer = :dlr)  \n" +
                "ORDER BY ID DESC";
        Map<String, Object> params = Maps.newHashMap();
        params.put("dlr", dlr);
        params.put("prodCode", prodCode);
        // 只返回最新的一条调车事务
        List reList = this.getList(sql, params);
        if (reList.size() == 0) {
            return reList;
        } else {
            return reList.subList(0, 1);
        }
    }

    @Override
    public List getInnerDetails(String productNo) {
        String sql = "SELECT   \n" +
                "  b.`人员姓名`,  \n" +
                "  e.`预计到店日期` AS `匹配预计到店日期`,  \n" +
                "  (SELECT g.`经销商名` FROM `lsh经销商表` g WHERE g.`许可_BMBS` = a.`调入经销商`) AS 调入经销商,  \n" +
                "  c.custom_name,  \n" +
                "  c.custom_tel,  \n" +
                "  a.`调车理由`,  \n" +
                "  c.expectedCustomPurchasing,  \n" +
                "  a.`生产号`,  \n" +
                "  a.`车架号`,  \n" +
                "  a.`车辆物流状态`,  \n" +
                "  c.expectedInStock,  \n" +
                "  (SELECT e.`经销商名` FROM `lsh经销商表` e WHERE e.`许可_BMBS` = if(a.`调出经销商` is null or a.调出经销商 = '', a.所属经销商, a.调出经销商)) AS 调出经销商,  \n" +
                "  d.`车型详细`,  \n" +
                "  a.`加配配置`,  \n" +
                "  if(a.协商调车款项金额 is null, a.`采购价格未税`, a.协商调车款项金额) as '采购价格未税',  \n" +
                "  a.`实际MSRP含税` / 1.17 as 实际MSRP含税,  \n" +
                "  a.`库龄天数`,  \n" +
                "  a.`协商调车款项金额`,  \n" +
                "  c.expectedLateInStockDate,  \n" +
                "  c.expectedLateMoneyInAccount,  \n" +
                "  a.`库存编号`,  \n" +
                "  if(a.调出经销商 is null or a.调出经销商 = '', a.`所属经销商`, a.调出经销商) AS 调出经销商许可,  \n" +
                "  c.is_emegency,  \n" +
                "  a.`调车物流状态`,  \n" +
                "  a.`调入经销商` AS 调入经销商许可,  \n" +
                "  e.`库存编号` AS `匹配库存编号`,  \n" +
                "  e.`生产号` AS `匹配生产号`,  \n" +
                "  e.`车架号` AS `匹配车架号`,  \n" +
                "  e.`车辆物流状态` AS `匹配车辆物流状态`   \n" +
                "FROM `LSH库存表` AS a   \n" +
                "LEFT JOIN `用户` AS b ON a.`申请销售顾问` = b.`用户名`   \n" +
                "LEFT JOIN (select * from car_transfer_log a  \n" +
                "	join (select max(id) maxId from car_transfer_log group by stock_code) b on a.id = b.maxId) AS c ON a.`库存编号` = c.stock_code   \n" +
                "LEFT JOIN `LSH车型` AS d ON a.`车型编号` = d.`车型编号`   \n" +
                "LEFT JOIN `lsh库存表` AS e ON c.compare_stock_code = e.`库存编号`   \n" +
                "WHERE a.`生产号` = :productNo   \n";
        Map params = Maps.newHashMap();
        params.put("productNo", productNo);
        return this.getList(sql, params);
    }

    @Override
    public List selectExpectGiveCarDateAndReason(String vin) {
        String sql = "SELECT a.`预计交车日期`, b.`调车理由`, c.`人员姓名` from `报价单表` AS a LEFT JOIN `LSH库存表` AS b ON a.`报价编号` = b.`报价编号` LEFT JOIN `用户` AS c ON b.`申请销售顾问` = c.`用户名` WHERE b.`车架号` = :vin";
        Map params = Maps.newHashMap();
        params.put("vin", vin);
        return this.getList(sql, params);
    }

    @Override
    public List getInnerData(String param, String type) {
        String sql = "";
        if ("stockCode".equalsIgnoreCase(type)) {
            sql = "SELECT custom_name,custom_tel,expectedLateInStockDate,expectedLateMoneyInAccount,expectedCustomPurchasing,expectedInStock FROM car_transfer_log WHERE stock_code = :param";
        } else {
            sql = "SELECT a.`调入经销商`, b.`车型详细`, a.`加配配置`, a.`车架号`, a.`采购价格含税`, a.`实际MSRP含税`, a.`库龄天数`, a.`协商调车款项金额`, c.expectedLateInStockDate, c.expectedLateMoneyInAccount, a.`库存编号` FROM `LSH库存表` AS a LEFT JOIN `LSH车型` AS b ON a.`车型编号` = b.`车型编号` LEFT JOIN car_transfer_log AS c ON a.`库存编号` = c.stock_code WHERE a.`生产号` = :param";
        }
        Map params = Maps.newHashMap();
        params.put("param", param);
        return this.getList(sql, params);
    }

    @Override
    /*更改日期2016/11/4 */
    public List getOuterVisableLevel(String productNo) {
       /* Map params = Maps.newHashMap();
        String sqlStr = "SELECT `外部可见级别` "
                + "FROM `LSH库存表` "
                + "WHERE `生产号` = :productNo";

        params.put("productNo", productNo);
        return this.getList(sqlStr, params);*/

        String[] pNo = productNo.split(",");
        String sqlStr = "SELECT  a.`外部可见级别` FROM `LSH库存表` as  a " +
                "   WHERE   ";
        for (int i = 0; i < pNo.length; i++) {
            if (i == 0) {
                sqlStr += "  a.`生产号` = '" + pNo[i] + "' ";
            } else {
                sqlStr += " or  a.`生产号` = '" + pNo[i] + "' ";
            }
        }
        sqlStr += "  group by  `外部可见级别` ";
        List list = this.getList(sqlStr);
        if (list.size() == 1) {
            List result = new ArrayList();
            Map re = Maps.newHashMap();
            return list;
        } else {
            List result = new ArrayList();
            Map re = Maps.newHashMap();
            re.put("外部可见级别", null);
            result.add(re);
            return result;
        }
    }

    @Override
    //修改时间2016/11/4
    public List getInnerVisableRole(String productNo) {
       /* Map params = Maps.newHashMap();
        String sqlStr = "SELECT a.`内部可见角色` "
                + "FROM `LSH库存内部可见` AS a "
                + "LEFT JOIN `LSH库存表` AS b ON b.`库存编号` = a.`库存编号` "
                + "WHERE b.`生产号` = :productNo";

        params.put("productNo", productNo);
        return this.getList(sqlStr, params);*/

        String[] pNo = productNo.split(",");
        String sqlStr = " SELECT a.`内部可见角色`     FROM `LSH库存内部可见` AS a  " +
                "  LEFT JOIN `LSH库存表` AS b ON b.`库存编号` = a.`库存编号`  " +
                "  WHERE  ";
        for (int i = 0; i < pNo.length; i++) {
            if (i == 0) {
                sqlStr += "   b.`生产号` = '" + pNo[i] + "' ";
            } else {
                sqlStr += " or  b.`生产号` = '" + pNo[i] + "' ";
            }
        }
        sqlStr += " group by  a.`内部可见角色` ";
        List list = this.getList(sqlStr);
        if (list.size() == 1) {
            return list;
        } else {
            List result = new ArrayList();
            Map re = Maps.newHashMap();
            re.put("内部可见角色", null);
            result.add(re);
            return result;
        }
    }

    public List getKCNumByProductNum(String productNo) {
        String sql = "SELECT `库存编号` FROM `LSH库存表` WHERE `生产号` =:productNo";
        Map params = Maps.newHashMap();
        params.put("productNo", productNo);
        return this.getList(sql, params);
    }

    public List getSelfCarStatusList(String permit) {
        String sql = " SELECT `生产号`,`车辆物流状态` FROM `lsh库存表`  " +
                "  WHERE `所属经销商` =:permit AND `生产号`   " +
                "  IS NOT NULL AND `生产号` NOT LIKE 'IRISTechnologies%'   " +
                "   AND  (IFNULL(`出库日期`,'') = '' OR `出库日期` < '1971-01-01')  ";
        Map params = Maps.newHashMap();
        params.put("permit", permit);
        return this.getList(sql, params);


    }

    public List followCluesList(String permit, String date) {
        Map params = Maps.newHashMap();
        params.put("permit", permit);
        params.put("date", date);
        StringBuffer sqlStr = new StringBuffer(
                "    SELECT * FROM  ( SELECT    " +
                        "    b.`顾客姓名` '线索姓名', " +
                        "    a.`电话号码` '电话号码', " +
                        "    b.`留档日期`  '线索创建日期', " +
                        "    b.`顾客首次渠道` '来源渠道', " +
                        "    f.`客户级别`   '意向级别', " +
                        "    c. `车型`   '意向车型', " +
                        "    a.`销售顾问` '销售顾问', " +
                        "    d.`人员姓名`  '销售顾问姓名', " +
                        "    e.`组名`  '销售组', " +
                        "    k.duration '当天跟进通话时长 (秒)', " +
                        "    a.`跟进备注` '当天跟进备注', " +
                        "    j.`跟进日期`  '上一次跟进日期', " +
                        "    j.duration '上一次跟进通话时长 (秒)', " +
                        "    j.`跟进备注` '上一次跟进备注'," +
                        "    b.`上次进店日期`   '最近一次进店日期', " +
                        "    h.`订单日期`  '订单日期', " +
                        "    i.`开票时间` '开票日期', " +
                        "    a.`更新时间`  '更新时间' " +
                        " FROM " +
                        "    `跟进记录表` AS a " +
                        "  JOIN  `顾客记录汇总` AS b  ON  a.`电话号码`=b.`电话号码`   and a.`许可`=b.许可 " +
                        "  LEFT JOIN  `lsh车型`  AS c ON  c.`车型编号`=b.`意向车型A` " +
                        "  LEFT JOIN   `用户`  AS  d  ON  d.`用户名`=a.`销售顾问` " +
                        "  LEFT JOIN  `销售顾问分组`  AS  e  ON e.`销售顾问` =d.`用户名`     " +
                        "  LEFT JOIN  `顾客进店记录表`  AS f  ON  f.`顾客编号`=b.`顾客编号` " +
                        "  LEFT JOIN `报价单表`   AS  g  ON  g.`顾客编号`=f.`顾客编号` " +
                        "  LEFT JOIN  `顾客订单记录` AS h ON  h.`订单编号`=g.`订单编号` " +
                        "  LEFT  JOIN  `报价交车表`  AS  i ON  i.`编号`=g.`交车` " +
                        "  LEFT JOIN  sc_call_history AS k ON ( k.date=a.`跟进日期` and k.tel=a.`电话号码`) " +
                        "  LEFT JOIN ( " +
                        "    SELECT " +
                        "      MAX(a.跟进日期) AS 跟进日期, " +
                        "      a.`电话号码`, " +
                        "      a.许可, " +
                        "      a.`跟进备注`, " +
                        "      b.duration " +
                        "    FROM " +
                        "       `跟进记录表` a " +
                        "      LEFT JOIN  sc_call_history AS b ON ( b.date=a.`跟进日期` and b.tel=a.`电话号码`) " +
                        "    WHERE " +
                        "       a.`跟进日期` <> :date " +
                        "    GROUP BY " +
                        "      a.`电话号码` " +
                        "      )  AS j " +
                        "   ON  ( j.`许可` = a.`许可` AND j.`电话号码` = a.`电话号码`) " +
                        " WHERE  ( a.`模式`='跟进'  or  a.`模式`='邀约' ) and a.`许可`=:permit  and a.`跟进日期`=:date " +
                        "   UNION ALL " +
                        " SELECT   " +
                        "   b.`顾客姓名` '线索姓名', " +
                        "   a.`电话号码` '电话号码', " +
                        "   b.`线索创建日期`  '线索创建日期', " +
                        "   b.`渠道` '来源渠道', " +
                        "   b.`客户级别`   '意向级别', " +
                        "   c. `车型`   '意向车型', " +
                        "   a.`销售顾问` '销售顾问', " +
                        "   d.`人员姓名`  '销售顾问姓名', " +
                        "   e.`组名`  '销售组', " +
                        "   k.duration '当天跟进通话时长 (秒)', " +
                        "   a.`跟进备注` '当天跟进备注', " +
                        "   j.`跟进日期`  '上一次跟进日期', " +
                        "   j.duration '上一次跟进通话时长 (秒)', " +
                        "   j.`跟进备注` '上一次跟进备注', " +
                 /*       "   #a.`跟进备注` '跟进备注', " +
                        "   #a.`跟进日期`  '跟进日期', " +*/
                        "   ' '   最近一次进店日期, " +
                        "   ' '  订单日期, " +
                        "   '' 开票日期, " +
                        "   a.`更新时间`  '更新时间'  " +
                        " FROM " +
                        "  `跟进记录表` AS a " +
                        "  JOIN  `lsh导入名单` AS b  ON  a.`电话号码`=b.`电话号码`   and a.`许可`=b.许可 " +
                        "  LEFT  JOIN  `lsh车型`  AS c ON  c.`车型编号`=b.`车型编号` " +
                        "  LEFT  JOIN   `用户`  AS  d  ON  d.`用户名`=a.`销售顾问` " +
                        "  LEFT JOIN  `销售顾问分组`  AS  e  ON e.`销售顾问` =d.`用户名`   " +
                        "  LEFT JOIN  sc_call_history AS k ON ( k.date=a.`跟进日期` and k.tel=a.`电话号码`) " +
                        "  LEFT JOIN ( " +
                        "    SELECT " +
                        "      MAX(a.跟进日期) AS 跟进日期, " +
                        "      a.`电话号码`, " +
                        "      a.许可, " +
                        "      a.`跟进备注`, " +
                        "      b.duration " +
                        "    FROM " +
                        "       `跟进记录表` a " +
                        "      LEFT JOIN  sc_call_history AS b ON ( b.date=a.`跟进日期` and b.tel=a.`电话号码`) " +
                        "    WHERE " +
                        "       a.`跟进日期` <> :date " +
                        "    GROUP BY " +
                        "      a.`电话号码` " +
                        "      )  AS j " +
                        "  ON  ( j.`许可` = a.`许可` AND j.`电话号码` = a.`电话号码`) " +
                        "  WHERE  ( a.`模式`='跟进'  or  a.`模式`='邀约' )  and a.`许可`=:permit  and a.`跟进日期`=:date " +
                        ")as a \n" +
                        " GROUP BY a.`电话号码`,a.`当天跟进通话时长 (秒)` ; ");
        return this.getList(sqlStr.toString(), params);
    }

    public List getRoleAndpermitAndname(String sheng) {

        Map params = Maps.newHashMap();
        params.put("sheng", sheng);
        StringBuffer sqlStr = new StringBuffer(
                "  select  " +
                        "b.许可,b.角色,b.`人员姓名`,b.`人员电话`,b.`用户名`,c.`省份`,c.经销商名  " +
                        "from `用户` b  " +
                        "left join  " +
                        "(  " +
                        "  select a.许可_bmbs as 许可 , a.`省份` as 省份 , a.经销商名 as 经销商名 from   " +
                        "`lsh经销商表` a where a.省份=:sheng   " +
                        ") c  on c.许可=b.许可  " +
                        "where   " +
                        "(b.`角色`='总经理' or b.`角色`='销售经理')  and c.省份=:sheng  ");
        return this.getList(sqlStr.toString(), params);
    }

    public List findDealerInfoById(String quoteId) {
        Map params = Maps.newHashMap();
        params.put("quoteId", quoteId);
        StringBuffer sqlStr = new StringBuffer(
                " select  '后市场明细' `类型`,b.名称,b.数量 ,d.`产品分类`  信息,b.折后价*b.数量  价格,b.`原价`*b.数量 原价 from 报价单表  a \n" +
                        "                       LEFT join `报价精品详细表`  b  on  a.精品=b.编号 \n" +
                        "                       LEFT JOIN lsh套餐信息  c   on   b.ID=c.套餐编号 \n" +
                        "                       LEFT  JOIN lsh精品分类 d  on  c.分类编号=d.分类编号 \n" +
                        "                       where   a.报价编号=:quoteId \n" +

                        "                      UNION all \n" +

                        "                       select  '赠送明细' `类型` ,a.随车名称 名称,ROUND(a.`随车内容`,2) 数量,a.`随车条件` 信息,a.随车成本 价格,0 `原价` from   `报价随车表` a \n" +
                        "                       where    a.报价编号=:quoteId ");
        return this.getList(sqlStr.toString(), params);
    }

    @Override
    public List findPermitAndAh() {
        String sql = "select   a.`经销商全称` ,a.`许可_BMBS` ,a.`许可_lsh`   \n" +
                " from lsh经销商表 a \n" +
                " where    \n" +
                " a.功能类型='AH'    ";
        return this.getList(sql);
    }

    @Override
    public List findAllCarType() {
        String  sql="select  车型编号,a.`Build_date`,Type,Variant, a.Factory_OP2 原厂配置 from lsh车型  a   where    a.车型详细 is  not   null  and    a.车型详细  <>\"\"    and Type=2  and   a.车型详细  is not null and Variant is  not  null";

        return this.getList(sql);
    }

    public PageData getDealerBatchInfo(SalesService.GetSaleListRequests req, String permit, String username) {

        String sql = "SELECT * FROM lsh经销商表 a where 1=1  ";
        if (!Strings.isNullOrEmpty(req.area1)) {
            sql += " and a.大区  like  '%" + req.area1 + "%' ";
        }
        if (!Strings.isNullOrEmpty(req.area2)) {
            sql += " and a.小区  like  '%" + req.area2 + "%' ";
        }
        if (!Strings.isNullOrEmpty(req.city)) {
            sql += " and a.城市 like  '%" + req.city + "%' ";
        }
        if (!Strings.isNullOrEmpty(req.province)) {
            sql += " and a.省份  like  '%" + req.province + "%' ";
        }
        if (!Strings.isNullOrEmpty(req.dealerlogo)) {
            sql += " and a.许可_BMBS like  '%" + req.dealerlogo + "%' ";
        }
        if (!Strings.isNullOrEmpty(req.dealername)) {
            sql += " and a.经销商全称   like  '%" + req.dealername + "%' ";
        }
        sql+="  order by a.更新时间 desc";
        Map params = Maps.newHashMap();

        return this.getPage(sql, params, req.getPageSize(), req.getPageIndex());
    }

    public List getAllCTAlists() {
        String sql = "  SELECT \n" +
                "a.CTA ,a.CTA_EN,count(distinct a.CTA)  " +
                "FROM  " +
                "  Lsh经销商表  a " +
                "WHERE\n" +
                "  a.cta IS NOT NULL  " +
                "AND a.cta <> ''  \n" +
                "group by a.CTA  \n" +
                "ORDER BY CONVERT( a.CTA  USING gbk ) COLLATE gbk_chinese_ci ASC     ";
        return this.getList(sql);
    }

    public List getAllBigArealists() {
        String sql = "select dealer_name 大区,permit 大区_EN from group_area where parent_id = 1 and status = 1";
        return this.getList(sql);
    }

    public List getAllSmallArealists(String daqu) {
        String sql = "   SELECT  DISTINCT a.小区 ,a.小区_EN     \n" +
                "               FROM    \n" +
                "                  Lsh经销商表  a   \n" +
                "               WHERE \n" +
                "                 a.小区 IS NOT NULL   \n" +
                "               AND a.小区 <> ''   \n" +
                "              and a.大区='" + daqu + "'\n" +
                "               ORDER BY CONVERT( a.小区  USING gbk ) COLLATE gbk_chinese_ci ASC     ";

        return this.getList(sql);
    }

    @Override
    public PageData getExchangeList(SalesService.ExchangeInfo req, String permit) {
        Map params = Maps.newHashMap();
        String sql = " select  车型.`车型编号`,\n" +
                " 车型.品牌,\n" +
                " 车型.来源,\n" +
                " 车型.级别,\n" +
                " `车型`.`车型`,\n" +
                " `车型`.车型详细,\n" +
                " 库存表.颜色,\n" +
                " 库存表.内饰,\n" +
                " 库存表.`车架号`,\n" +
                " 库存表.`库龄天数`,\n" +
                " 报价单表.`折后价`,\n" +
                " 库存表.`实际MSRP含税`,\n" +
                " 顾客汇总.`顾客姓名`   订单客户姓名 ,\n" +
                " 顾客汇总.`电话号码`  订单客户电话 ," +
                " 报价订单表.`订单签署日期`,\n" +
                " 报价单表.销售顾问,\n " +
                " 用户.人员姓名 销售顾问名," +
                "  用户.`人员电话`  销售顾问电话," +
                " log.update_time 换车日期,\n" +
                " 报价单表.`报价单号`,\n" +
                " 报价单表.`报价编号`,\n" +
                " 报价单表.`初始报价单号`,\n" +
                " 报价订单表.订单编号,\n" +
                " 报价订单表.订单状态 ," +
                " 库存表.`生产号`\n" +
                " from `报价订单表` 报价订单表  \n" +
                " JOIN `报价单表`  报价单表   ON  报价订单表.`订单编号`=报价单表.`订单编号`\n" +
                " JOIN vw_max_quotedcode_all 视图 on  报价单表.报价编号=视图.`报价编号`\n" +
                " join LSH库存表 库存表 on 库存表.库存编号=`报价单表`.库存编号\n" +
                " JOIN lsh车型 车型 on 库存表.`车型编号`=车型.`车型编号`\n" +
                " JOIN 顾客记录汇总 顾客汇总 on 顾客汇总.顾客编号=报价单表.顾客编号\n" +
                " left  JOIN `报价交车表`  交车表 on `交车表`.编号=报价单表.`交车`\n" +
                " JOIN `用户` 用户  on 用户.`用户名`=报价单表.销售顾问" +
                " LEFT JOIN ( \n" +
                "    SELECT b.update_time,b.quote_id \n" +
                "     FROM exchange_stock_log b \n" +
                "    JOIN ( SELECT MAX(id) mid FROM exchange_stock_log GROUP BY org_qid ) a ON a.mid = b.id ) log ON log.quote_id =报价单表.报价编号\n" +
                " where 报价订单表.`订单审批通过日期` is not  NULL\n" +
              /*  " and  `交车表`.合同提交时间 is NULL\n" +
                " and   `交车表`.`合同签约日期` is  NULL\n" +
                " and 报价订单表.订单状态 ='订单C'\n" +*/
                "  and 报价订单表.订单状态 in('订单C','合同O') " +
                " and  报价单表.许可=:permit\n" +
                "  and  报价单表.`是否驳回`='否' " ;
        params.put("permit", permit);
        if (!Strings.isNullOrEmpty(req.scName)) {
            sql=sql+"  and   用户.人员姓名 like:scName ";
            params.put("scName", "%" +req.scName + "%");
        }
        if (!Strings.isNullOrEmpty(req.customerName)) {
            sql=sql+"  and 顾客汇总.`顾客姓名` like:customerName  ";
            params.put("customerName", "%" + req.customerName+ "%");
        }
        if (!Strings.isNullOrEmpty(req.carType)) {
            sql=sql+"  and  (`车型`.车型详细 like:carType  or  `车型`.`车型` like:carType  or 车型.级别 like:carType   ) ";
            params.put("carType", "%" +req.carType + "%");
        }
        if (!Strings.isNullOrEmpty(req.minSignDate)) {
            sql=sql+" and 报价订单表.`订单签署日期`>=:minSignDate  ";
            params.put("minSignDate", req.minSignDate);
        }
        if (!Strings.isNullOrEmpty(req.bigSignDate)) {
            sql=sql+"and  报价订单表.`订单签署日期`<=:bigSignDate  ";
            params.put("bigSignDate", req.bigSignDate);
        }
        sql=sql+"  order by  报价订单表.`订单签署日期` desc  ";
        return this.getPage(sql, params, req.getPageSize(), req.getPageIndex());
    }

    @Override
    public PageData getChangeCarList(SalesService.ChangeCarInfo info, String permit) {
        Map params = Maps.newHashMap();
        params.put("permit", permit);
        String sql =
                " select \n" +
                        " 车型.品牌,\n" +
                        " 车型.来源,\n" +
                        " 车型.级别,\n" +
                        " 车型.车型,\n" +
                        " 车型.车型详细,\n" +
                        " 库存表.颜色,\n" +
                        " 库存表.内饰,\n" +
                        " 库存表.车架号,\n" +
                        " 库存表.库龄天数,\n" +
                        " 库存表.实际MSRP含税,\n" +
                        "  库存表.库存编号,\n" +
                        " 库存表.生产号\n" +
                        " from  \n" +
                        " lsh库存表 库存表\n" +
                        " JOIN  lsh车型 车型 on 库存表.`车型编号`=车型.`车型编号` \n" +
                        " where  \n" +
                        " 库存表.生产号  not LIKE '%IRISTech%'\n" +
                        " and 库存表.车架号  not LIKE '%IRISTech%'\n" +
                        " and 库存表.车辆零售状态 ='未卖'\n" +
                        " and 库存表.报价编号 is null" +
                        "  and  库存表.`所属经销商`=:permit\n"

                        + " and 车型.品牌=:brand  \n" +
                        " and  车型.来源=:source \n" +
                        " and 车型.级别=:series \n" +
                        " and  `车型`.`车型`=:carType  \n" +
                        " and `车型`.车型详细=:carParticulars  " +
                        " and 实际MSRP含税=:msrp \n ";


        params.put("brand", info.brand);//品牌
        params.put("source", info.source);//来源
        params.put("carParticulars", info.carParticulars);//车型详细
        params.put("carType", info.carType);//车型
        params.put("series", info.series);//级别
        params.put("msrp", info.msrp);//msrp
        return this.getPage(sql, params, info.getPageSize(), info.getPageIndex());
    }

    @Override
    public 报价单表Model getModelByRestore(String initQuoteNumber) {
        Map params = Maps.newHashMap();
        params.put("initQuoteNumber", initQuoteNumber);
        String sql = "select m from 报价单表Model m where m.初始报价单号 =:initQuoteNumber" +
                "AND m.是否还原点='是' AND m.是否驳回 ='否' order by m.报价编号 desc";
        List<报价单表Model> list = this.getEntityList(sql,params);

        return  list != null && list.size() >0?list.get(0):null;
    }

    private double toDouble(Object value){
        if (value == null || "".equals(value.toString().trim())) return 0;

        return Double.valueOf(value.toString());
    }

    @Override
    public List getContractData(String prodNum) {
        String sql = "select " +
                "  a.车架号 vin,   \n" +
                "  trim(c.合同号) as contract_code,  \n" +
                "  trim(c.邮箱) as email_1,  \n" +
                "  trim(c.详细地址) as address_1,  \n" +
                "  trim(c.邮编) as zipcode_1,  \n" +
                "  trim(c.开票人性别) as sex_1,  \n" +
                "  trim(c.开票客户电话) as cell_1,  \n" +
                "  trim(c.开票客户姓名) as register_name,  \n" +
                "  if(c.开票客户身份证类型 = '个人', '身份证', '组织机构代码') as id_type,  \n" +
                "  if(c.开票客户身份证类型 = '个人', trim(c.开票客户身份证), trim(c.开票客户机构代码)) as id_num,  \n" +
                "  (select trim(名称) from 报价客户表 where 编号 = b.客户详细) as order_name,  \n" +
                "  (select trim(顾客性别) from 顾客记录汇总 where 顾客编号 = b.顾客编号) as sex_2,  \n" +
                "  (select trim(电话) from 报价客户表 where 编号 = b.客户详细) as cell_2,  \n" +
                "  (select trim(身份证类型) from 报价客户表 where 编号 = b.客户详细) as id_type_2,  \n" +
                "  (select trim(身份证) from 报价客户表 where 编号 = b.客户详细) as id_num_2,  \n" +
                "  (select trim(详细地址) from 报价客户表 where 编号 = b.客户详细) as address_2,  \n" +
                "  (select trim(邮编) from 报价客户表 where 编号 = b.客户详细) as zipcode_2,  \n" +
                "  (select trim(邮箱) from 报价客户表 where 编号 = b.客户详细) as email_2,  \n" +
                "  (select trim(级别) from lsh车型 where 车型编号 = a.车型编号) as car_name,  \n" +
                "  (select concat(trim(级别), ' ', trim(车型)) from lsh车型 where 车型编号 = a.车型编号) as car_model,  \n" +
                "  (select trim(车型详细) from lsh车型 where 车型编号 = a.车型编号) as car_sfx,  \n" +
                "  trim(b.外观) as car_color,  \n" +
                "  b.车辆销售价 as neat_car_price,  \n" +
                "  b.消费税 as consumer_tax,  \n" +
                "  b.折后价 as car_total,  \n" +
                "  ifnull((select 购置税 from 报价预收表 where 预收编号 = b.预收编号), 0) as purchase_tax,  \n" +
                "  ifnull((select 折后价 from 报价保险详细表 where 保险编号 = b.保险编号 and 险种 = '车船税' and 第N年 = 1), 0) as vehicle_vessel_tax,  \n" +
                "  ifnull((select 保险折后价 from 报价保险表 where 保险编号 = b.保险编号), 0) as insurance_amount,  \n" +
                "  ifnull((select cast(费用折后价 as decimal(17,5)) from 报价费用详细表 where 费用编号 = b.费用编号 and 费用名称 = '上牌服务费'), 0) as license_fee,  \n" +
                "  ifnull((select ifnull(折后价, 0) * ifnull(数量, 0) from 报价精品详细表 aa, lsh精品套餐详细 bb, lsh精品信息 cc where aa.id = bb.套餐编号 and bb.精品编号 = cc.精品编号 and cc.分类编号 = 63 and aa.编号 = b.精品), 0) as glass_repair,  \n" +
                "  ifnull((select 折后价 from 报价保险详细表 where 保险编号 = b.保险编号 and 险种 = '全车盗抢险' and 第N年 = 1), 0) as theft_robbery,  \n" +
                "  ifnull((select ifnull(cast(延保折后价 as decimal(17,5)), 0) + ifnull(cast(厂家延保实收 as decimal(17,5)), 0) + ifnull(cast(第三方延保实收 as decimal(17,5)), 0)  \n" +
                "     from 报价五星延保 where 延保编号 = b.延保编号), 0) as ex_insurance_fee,  \n" +
                "  ifnull((select 置换手续费 from 报价置换表 where 置换编号 = b.置换编号), 0) as second_hand,  \n" +
                "  ifnull((select cast(金融服务费 as decimal(17,5)) from 报价金融表 where 金融编号 = b.金融), 0) as loan_fee,  \n" +
                "  ifnull((select cast(费用折后价 as decimal(17,5)) from 报价费用详细表 where 费用编号 = b.费用编号 and 费用名称 = '其他服务费'), 0) as sales_fee,  \n" +
                "  ifnull((select sum(ifnull(折后价, 0) * ifnull(数量, 0)) from 报价精品详细表 where 编号 = b.精品), 0) as freebie_fee,  \n" +
                "  ifnull((select ifnull(其他一, 0) + ifnull(其他二, 0) + ifnull(其他三, 0) + ifnull(其他四, 0) + ifnull(其他五, 0) from 报价预收表 where 预收编号 = b.预收编号), 0) as other,  \n" +
                "  ifnull((select if(订金类型 = '订金', 订金金额, 0.00) from 报价订单表 where 订单编号 = b.订单编号), 0) as order_amount,  \n" +
                "  ifnull((select 置换估价 from 报价置换表 where 置换编号 = b.置换编号), 0) as second_hand_price,  \n" +
                "  (select 经销商全称 from lsh经销商表 where 许可_bmbs = b.许可) as dealer_name,  \n" +
                "  (select 经销商地址 from lsh经销商表 where 许可_bmbs = b.许可) as dealer_address,  \n" +
                "  (select 经销商电话 from lsh经销商表 where 许可_bmbs = b.许可) as dealer_tel,  \n" +
                "  (select 人员姓名 from 用户 where 用户名 = b.销售顾问) as sc_name  \n" +
                "from lsh库存表 a  \n" +
                "join 报价单表 b on a.报价编号 = b.报价编号  \n" +
                "join 报价交车表 c on b.交车 = c.编号  \n" +
                "where a.生产号 = :prodNum  \n";
        Map<String, Object> params = new HashMap<>();
        params.put("prodNum", prodNum);
        return this.getList(sql, params);
    }

    @Override
    public List getContractDataFreebies(String prodNum) {
        String sql = "select  \n" +
                "  c.名称 as freebie_name,  \n" +
                "  c.数量 as quantity,  \n" +
                "  c.原价 * 数量 as ori_price,  \n" +
                "  c.折后价 * 数量 as dis_price  \n" +
                "from lsh库存表 a  \n" +
                "join 报价单表 b on a.报价编号 = b.报价编号  \n" +
                "join 报价精品详细表 c on b.精品 = c.编号  \n" +
                "where a.生产号 = :prodNum  \n";
        Map<String, Object> params = new HashMap<>();
        params.put("prodNum", prodNum);
        return this.getList(sql, params);
    }

    @Override
    public PageData getSaleTailAfter(String permit, SalesService.SaleTailAfter request,RequestBase RBase) {
        Map map = Maps.newHashMap();

        String sql = "SELECT *  FROM ( SELECT " +
                "  IF (  a.in_dealer = :permit ,'调入', '调出') AS '调车类型', " +
                "  c.品牌 AS '品牌', " +
                "  c.来源, " +
                "  b.`采购批发商` AS '采购经销商', " +
                "  IF(ISNULL(d.id),'否','是') AS '是否预装', " +
                "  c.级别 AS '车系',\n" +
                "  c.车型,\n" +
                "  c.`车型详细` AS '车款',b.`车款年份`," +
                "  b.`生产号`,\n" +
                "  b.`车架号` AS '车架号(VIN)',\n" +
                "  b.`欧版底盘号` AS '欧版底盘号(FIN)',\n" +
                "  b.`发动机号`,\n" +
                "  b.`VSB号`,\n" +
                "  b.`颜色代码`,\n" +
                "  b.颜色,\n" +
                "  b.`内饰代码`,\n" +
                "  b.内饰,\n" +
                "  b.`加配配置`,\n" +
                "  b.加配价格含税 AS '加配价格(含税)'," +
                "  b.`采购价格含税` AS '采购价格(含税)',\n" +
                "  b.`采购价格未税` AS '采购价格(不含税)',\n" +
                "  b.`协商调车款项金额` AS '调车协商价格',\n" +
                "  b.`实际MSRP含税` AS '实际MSRP(含税)',\n" +
                "  b.`港口名称`,\n" +
                "  b.`批发日期` AS '采购日期', " +
                "  b.`批发付款到期日`,\n" +
                "  b.`库龄天数`,\n" +
                "  b.`商检单到店状态`,\n" +
                "  b.`关单到店状态`, b.合格证到店状态, \n" +
                "  b.`商检单号` AS '商检单到店日期',\n" +
                "  b.`关单号` AS '关单到店日期',\n" +
                "  b.`所属经销商`,\n" +
                "  a.custom_name AS '订单客户',\n" +
                "  b.`申请销售顾问`,\n" +
                "  a.expectedCustomPurchasing AS '客户预计购车日期',\n" +
                "  a.is_emegency AS '是否紧急调车',\n" +
                "  a.expectedLateInStockDate AS '期望最晚入库日期',\n" +
                "  a.expectedLateMoneyInAccount AS '最晚款项到账日期',\n" +
                "  a.logistics_status AS '调车车辆物流状态',\n" +
                "  a.in_dealer AS '调入经销商',\n" +
                "  a.out_dealer AS '调出经销商', " +
                "  a.modify_date AS '更新时间',b.`库存编号` " +
                " FROM ( SELECT * FROM car_transfer_log a JOIN  " +
                "  ( SELECT   max(id) maxId FROM  car_transfer_log " +
                "  GROUP BY stock_code ) b ON a.id = b.maxId ) AS a " +
                " LEFT JOIN `lsh库存表` AS b ON a.stock_code = b.`库存编号`\n" +
                " LEFT JOIN `lsh车型` AS c ON b.`车型编号` = c.`车型编号` " +
                " LEFT JOIN custom_car_configure d ON b.`库存编号`=d.store_id AND d.`status`=1 " +
                " WHERE is_delete!=1 AND !ISNULL(b.`车型编号`) " +
                " ) a  WHERE 1 = 1 " ;

        if (!Strings.isNullOrEmpty(request.VIN)){
            sql = sql + " AND a.`车架号(VIN)` LIKE :VIN ";
            map.put("VIN","%" + request.VIN + "%");
        }
        if (!Strings.isNullOrEmpty(request.productNo)){
            sql = sql + " AND a.`生产号` LIKE :productNo " ;
            map.put("productNo","%" + request.productNo + "%");
        }
        if (!Strings.isNullOrEmpty(request.ShuntingType)){
            sql = sql + " AND a.调车类型 LIKE :ShuntingType " ;
            map.put("ShuntingType","%" + request.ShuntingType + "%");
        }
        if (!Strings.isNullOrEmpty(request.outerStatus)){
            sql = sql + " AND a.`调车车辆物流状态` LIKE :outerStatus " ;
            map.put("outerStatus","%" + request.outerStatus + "%");
        }
        if (!Strings.isNullOrEmpty(request.custName)){
            sql = sql + " AND a.`订单客户` LIKE :custName " ;
            map.put("custName","%" + request.custName + "%");
        }
        if (!Strings.isNullOrEmpty(request.guest)){
            sql = sql + " AND a.`申请销售顾问` LIKE :guest";
            map.put("guest","%" + request.guest + "%");
        }
        //（1）查询条件只有调入经销商 （2）查询条件只有调出经销商 （3）调出调如经销商都有
        // （4） 特殊：调入调出条件没有的话，则取所有调出调入的信息。则用许可作为条件
        if (!Strings.isNullOrEmpty(request.innerDealer) && Strings.isNullOrEmpty(request.outerDealer)){
            sql += " AND a.`调入经销商` LIKE :innerDealer ";
            map.put("innerDealer","%" + request.innerDealer + "%");
        }else if (!Strings.isNullOrEmpty(request.outerDealer) && Strings.isNullOrEmpty(request.innerDealer)){
            sql +=" AND a.`调出经销商` LIKE :outerDealer ";
            map.put("outerDealer","%" + request.outerDealer + "%");
        }else if (!Strings.isNullOrEmpty(request.outerDealer) && !Strings.isNullOrEmpty(request.innerDealer)){
            sql+=" AND ( `调出经销商` LIKE :outerDealer OR `调入经销商` LIKE :innerDealer) " ;
            map.put("innerDealer","%" + request.innerDealer + "%");
            map.put("outerDealer","%" + request.outerDealer + "%");
        }else {
            sql+=" AND ( `调出经销商` = :permit OR `调入经销商` = :permit) " ;

        }
        map.put("permit",permit);

        sql = sql + " ORDER BY  a.`更新时间` DESC ";

        return this.getPage(sql , map,RBase.getPageSize(),RBase.getPageIndex());
    }

    @Override
    public PageData getInventoryInfo(String dealer, Map map, RequestBase request) {
        long startTime = System.currentTimeMillis();

        try {
            String sql = "SELECT * FROM " +
                    " (SELECT " +
                    " `LSH库存表a`.`生产日期`, `LSH库存表a`.`车款年份`, `LSH库存表a`.car_state, " +
                    //" IF (`LSH库存表a`.`生产号` LIKE '%IRISTechNV_%'  OR `LSH库存表a`.`生产号` LIKE '%IRISTechnologies%','',  `LSH库存表a`.`生产号`) `生产号`, " +
                    " `LSH库存表a`.`生产号`, "+
                    " IF (`LSH库存表a`.`车架号` LIKE '%IRISTechNV_%'  OR `LSH库存表a`.`车架号` LIKE '%IRISTechnologies%','',  `LSH库存表a`.`车架号`) `车架号（VIN）`," +
                    " `LSH库存表a`.`欧版底盘号` `欧版底盘号（FIN)`, " +
                    " `LSH库存表a`.`发动机号`, `LSH库存表a`.`VSB号`, `LSH库存表a`.`库存编号`, " +
                    " `LSH库存表a`.`颜色代码`, `LSH库存表a`.`颜色`, " +
                    " `LSH库存表a`.`内饰代码`, `LSH库存表a`.`内饰`, " +
                    " `LSH库存表a`.`原厂配置`, `LSH库存表a`.`加配配置`, " +
                    " `LSH库存表a`.`加配价格含税` `加配价格（含税）`, IFNULL(`LSH库存表a`.`采购价格含税`,0) `采购价格（含税）`, " +
                    " IFNULL(`LSH库存表a`.`采购价格未税`,0) `采购价格（不含税）`, " +
                    " `LSH库存表a`.`标配MSRP含税` `标配MSRP（含税）`, `LSH库存表a`.`实际MSRP含税` `实际MSRP（含税）`, " +
                    " `LSH库存表a`.`厂家确认订单日期`, `LSH库存表a`.`预计到港日期` `预计到港日期（ETA）`, " +
                    " `LSH库存表a`.`实际到港日期` `实际到港日期（ATA）`, `LSH库存表a`.`预计下线日期`, " +
                    " `LSH库存表a`.`港口代码`, `LSH库存表a`.`港口名称`, " +
                    " `LSH库存表a`.`车辆物流状态`, `LSH库存表a`.`车辆零售状态`, " +
                    " `报价订单表`.`销售类型` 车辆零售类型, `LSH库存表a`.`HOLD日期`, " +
                    " `报价单表`.`报价编号`, " +
                    " `LSH库存表a`.`申请释放日期`, `LSH库存表a`.`批发日期`, " +
                    " `LSH库存表a`.`批发付款到期日`, `LSH库存表a`.`NBI车辆状态`, " +
                    " `LSH库存表a`.`预计到店日期`, `LSH库存表a`.`店内车辆位置`, " +
                    " `LSH库存表a`.`二次PDI完成日期`, `LSH库存表a`.`二次PDI结果`, " +
                    " `LSH库存表a`.`入库日期`, `LSH库存表a`.`出库日期`, " +
                    //" datediff(NOW(),`LSH库存表a`.`批发日期`) 入库天数," +
                    " `LSH库存表a`.`库龄天数`, `LSH库存表a`.`建议三次PDI日期`, " +
                    " `LSH库存表a`.`三次PDI完成日期` `三次PDI 完成日期`, " +
                    " `LSH库存表a`.`三次PDI结果` `三次PDI 结果`, " +
                    " `LSH库存表a`.`车辆受损情况`, `LSH库存表a`.`商检单到店状态`, " +
                    " `LSH库存表a`.`关单到店状态`, `LSH库存表a`.`合格证到店状态`, " +
                    " `LSH库存表a`.`合格证号`, `LSH库存表a`.`批发商`, " +
                    " `LSH库存表a`.`外部可见级别`, LSH库存表a.协商调车款项金额 AS '调车协商价格' , " +
                    " `LSH库存表a`.`商检单号` `商检单到店日期`, `LSH库存表a`.`关单号` `关单到店日期`, " +
                    " `LSH车型`.`MSRP` `BI车款标配MSRP（含税）`, `LSH车型`.`品牌` `品牌`, " +
                    " `LSH车型`.`来源`, `LSH车型`.`级别` 车系, " +
                    " `LSH车型`.`车型`, `LSH车型`.`车型详细` 车款, " +
                    " `LSH库存表a`.`批发日期` `采购日期`, " +
                    " `LSH库存表a`.`SNP含税` `标配区域限价-SNP（含税）`,"+
                    " `报价交车表`.`开票时间` `开票日期`, " +
                    " `报价订单表`.`订单签署日期`, " +
                    " `所属经销商`.`经销商名` `所属经销商`, " +
                    " IF(`采购批发商`.`经销商名` IS NULL, LSH库存表a.采购批发商, `采购批发商`.`经销商名`) `采购经销商`, " +
                    " REPLACE(`LSH库存内部可见`.`内部可见角色`,'CDO','DCC') `内部可见级别`, " +
                    "   IF(`LSH库存表a`.`advance_sales_days` is null,'否','是') 是否提前销售,   " +
                    "   IF(`LSH库存表a`.`advance_sales_days` is null,null,`库存利润表`.`上报厂家日期`) 提前销售日期,  " +
                    "   `LSH库存表a`.`advance_sales_days` 提前销售天数," +
                    "   `LSH库存表a`.`route_days`  在途天数, `库存利润表`.`上报厂家日期`, " +
                    "   `LSH库存表a`.`库龄天数` 入库天数," +
                    "       (case when (`LSH库存表a`.`生产号` NOT LIKE '%IRISTechNV_%' OR `LSH库存表a`.`生产号` NOT LIKE '%IRISTechnologies%') and NBI车辆状态 = '在途库存'and 车辆零售状态='未卖' then '在途可售' " +
                    "           when (`LSH库存表a`.`生产号` NOT LIKE '%IRISTechNV_%' OR `LSH库存表a`.`生产号` NOT LIKE '%IRISTechnologies%') and NBI车辆状态 = '现货库存'and 车辆零售状态='未卖' then '在库可售' " +
                    "           when (`LSH库存表a`.`生产号` NOT LIKE '%IRISTechNV_%' OR `LSH库存表a`.`生产号` NOT LIKE '%IRISTechnologies%') and 报价交车表.开票时间 is not null and 报价交车表.`交车日期` is null then '已开票未提车' " +
                    "           when (`LSH库存表a`.`生产号` NOT LIKE '%IRISTechNV_%' OR `LSH库存表a`.`生产号` NOT LIKE '%IRISTechnologies%') and NBI车辆状态 = '在途库存' and (车辆零售状态='已卖' or 报价订单表.`订单申请日期` is not null ) then '在途已配' " +
                    "           when (`LSH库存表a`.`生产号` NOT LIKE '%IRISTechNV_%' OR `LSH库存表a`.`生产号` NOT LIKE '%IRISTechnologies%') and NBI车辆状态 = '现货库存'and (车辆零售状态='已卖' or 报价订单表.`订单申请日期` is not null ) then '在库已配'  " +
                    "   end)    车辆库存状态,   " +
                    "   `LSH库存表a`.car_number 车辆编号,   " +
                    "   `LSH库存表a`.warehouse 仓库,  " +
                    "   `LSH库存表a`.inventory_location 库存位置,   " +
                    "   `LSH库存表a`.remark 备注,   " +
                    "   (TO_DAYS(DATE_FORMAT(IF(报价交车表.开票时间 is null ,CURDATE(),报价交车表.开票时间),'%Y%m%d')) - TO_DAYS(IFNULL(exchange_stock_log.update_time,报价订单表.`订单申请日期`)) ) 配车天数, " +
                    "   IFNULL(exchange_stock_log.update_time,报价订单表.`订单申请日期`)  配车日期,   " +
                    "   IF(ISNULL(d.id),'否','是') AS '是否预装' " +
                    " FROM " +
                    " `LSH库存表` `LSH库存表a` " +
                    " LEFT JOIN  `LSH车型`  ON `LSH车型`.`车型编号` = `LSH库存表a`.`车型编号` " +
                    " LEFT JOIN " +
                    " ( " +
                    " select " +
                    " a.*                " +
                    " FROM " +
                    " (SELECT " +
                    " a.* , " +
                    " b.`订单状态` 订单状态  " +
                    " FROM " +
                    " 报价单表 a , " +
                    " 报价订单表  b   " +
                    " where " +
                    " A.许可=:dealer  " +
                    " and a.订单编号=b.`订单编号`  " +
                    " and 是否驳回 = '否'   " +
                    " order by " +
                    " `报价单号` desc  ) a  " +
                    " group by " +
                    " a.初始报价单号   ) 报价单表  " +
                    " ON `报价单表`.`库存编号`=  `LSH库存表a`.库存编号  " +
                    " and 报价单表.订单状态 in ('意向订单O','意向订单C','订单修改','订单C','订单O','合同O','合同C','开票O','开票C','换票C','换票O','退票O','合同修改','换票' ) " +
                    " LEFT JOIN `LSH经销商表` `所属经销商` ON 所属经销商.`许可_BMBS` = `LSH库存表a`.`所属经销商` " +
                    " LEFT JOIN `LSH经销商表` `采购批发商` ON 采购批发商.`许可_BMBS` = `LSH库存表a`.`采购批发商`  " +
                    " LEFT JOIN `报价订单表`  ON `报价单表`.`订单编号` = `报价订单表`.`订单编号` " +
                    " LEFT JOIN `库存利润表` ON `LSH库存表a`.`库存编号` = `库存利润表`.`库存编号` " +
                    " LEFT JOIN `报价交车表` ON `报价单表`.`交车` = `报价交车表`.`编号` " +
                    " LEFT JOIN `LSH库存内部可见` ON `LSH库存内部可见`.`库存编号` = `LSH库存表a`.`库存编号` AND ( `内部可见角色` = 'SC/CDO可见' OR `内部可见角色` = '' ) " +
                    " LEFT JOIN  (select org_qid,MAX(update_time) update_time from exchange_stock_log group by org_qid ) exchange_stock_log ON exchange_stock_log.org_qid = 报价单表.`初始报价单号` " +
                    " LEFT JOIN custom_car_configure d ON `LSH库存表a`.库存编号=d.store_id AND d.`status`=1 " +
                    " WHERE  ( " +
                    " ( " +
                    " `LSH库存表a`.所属经销商= :dealer) OR (`LSH库存表a`.调入经销商= :dealer  and `LSH库存表a`.调车物流状态 is not null and `LSH库存表a`.调车物流状态 <>'' " +
                    " )  " +
                    " OR ( `LSH库存表a`.调出经销商=:dealer and `LSH库存表a`.调车物流状态='调出出库') " +
                    " )   " +
                    " and ( `LSH库存表a`.is_delete = '0'  or `LSH库存表a`.is_delete is null )   " +
                    " GROUP BY " +
                    " LSH库存表a.车架号, " +
                    " `报价单表`.`报价编号` " +
                    " ) aa  " +
                    " where " +
                    " 1 = 1 AND aa.`生产号` NOT LIKE '%IRISTechnologies%' ";
            Map params = Maps.newHashMap();
            params.put("totalCount", map.get("totalCount") == null ? 0 : map.get("totalCount"));
            map.remove("totalCount");
            params.put("dealer", dealer);

            Iterator<Map.Entry<String, String>> it = map.entrySet().iterator();
            while (it.hasNext()) {
                Map.Entry<String, String> entry = it.next();
                //过滤掉前端传回的参数map中 "1":"1" 的情况
                if ("1".equals(entry.getKey())) {
                    sql += "  and (aa.car_state is null  or aa.car_state ='00')   ";
                    continue;
                }

                //(1)未批发 (2)已批发未零售 (3)已订单未零售 (4)未批发,已批发未零售 (5)已批发未零售,已订单未零售
                //前端三个多选框（ 未批发，已批发未零售，已订单未零售 ）
                if ("多选框".equals(entry.getKey())) {
                    if (!entry.getValue().contains(",")) {//只有一个条件
                        if ("未批发".equals(entry.getValue())) {//有生产日期无批发日期
                            sql += " AND ((aa.`生产日期` IS NOT NULL AND  aa.`生产日期`!='') AND (aa.`批发日期` IS NULL OR aa.`批发日期`='' )) ";
                        } else if ("已批发未零售".equals(entry.getValue())) {//有批发日期 无开票日期
                            sql += " AND ((aa.`批发日期` IS NOT NULL AND  aa.`批发日期`!='') AND (aa.`开票日期` IS NULL OR aa.`开票日期`='' )) ";
                        } else {//已订单未零售 有订单签署日期 无开票日期
                            sql += " AND ((aa.`订单签署日期` IS NOT NULL AND  aa.`订单签署日期`!='') AND (aa.`开票日期` IS NULL OR aa.`开票日期`='' )) ";
                        }
                        continue;
                    } else {
                        String[] str = entry.getValue().split(",");
                        int total=0;
                        sql += " AND (";
                        for (int i = 0; i < str.length; i++) {
                            if ("未批发".equals(str[i])) {//有生产日期无批发日期
                                sql += " ((aa.`生产日期` IS NOT NULL AND  aa.`生产日期`!='') AND (aa.`批发日期` IS NULL OR aa.`批发日期`='' )) ";
                                total=1;
                            } else if (total==1 && "已批发未零售".equals(str[i])) {//有批发日期 无开票日期
                                sql += " OR ((aa.`批发日期` IS NOT NULL AND  aa.`批发日期`!='') AND (aa.`开票日期` IS NULL OR aa.`开票日期`='' )) ";
                                total=1;
                            } else if (total==0 && "已批发未零售".equals(str[i])){
                                sql += " ((aa.`批发日期` IS NOT NULL AND  aa.`批发日期`!='') AND (aa.`开票日期` IS NULL OR aa.`开票日期`='' )) ";
                                total=1;
                            } else if (total==1 && "已订单未零售".equals(str[i])){//已订单未零售 有订单签署日期 无开票日期
                                sql += " OR ((aa.`订单签署日期` IS NOT NULL AND  aa.`订单签署日期`!='') AND (aa.`开票日期` IS NULL OR aa.`开票日期`='' )) ";
                            }else {
                                sql += "  ((aa.`订单签署日期` IS NOT NULL AND  aa.`订单签署日期`!='') AND (aa.`开票日期` IS NULL OR aa.`开票日期`='' )) ";
                            }
                        }
                        sql += " )";
                        continue;
                    }
                }


                if ((!"生产日期1".equals(entry.getKey()) && !"生产日期2".equals(entry.getKey())) &&
                        (!"批发日期1".equals(entry.getKey()) && !"批发日期2".equals(entry.getKey())) &&
                        (!"预计到港日期1".equals(entry.getKey()) && !"预计到港日期2".equals(entry.getKey())) &&
                        (!"库龄天数1".equals(entry.getKey()) && !"库龄天数2".equals(entry.getKey()))) {
                    if("车系".equals(entry.getKey())){//针对车系做的特别处理
                        sql+=" AND concat(if(aa.来源 = 'CBU', '', concat(aa.来源, ' ')), aa.车系) = :clazz ";
                        params.put("clazz", entry.getValue());
                    }else {
                        sql += " AND aa.`" + entry.getKey() + "` like '%" + entry.getValue() + "%' ";
                    }
                } else if ("生产日期1".equals(entry.getKey())) {
                    sql += " AND date_format(aa.`生产日期`,'%Y-%m-%d') >=date_format('" + entry.getValue() + "','%Y-%m-%d') ";
                } else if ("生产日期2".equals(entry.getKey())) {
                    sql += " AND date_format(aa.`生产日期`,'%Y-%m-%d') <=date_format('" + entry.getValue() + "','%Y-%m-%d')  ";
                } else if ("批发日期1".equals(entry.getKey())) {
                    sql += " AND date_format(aa.`批发日期`,'%Y-%m-%d') >=date_format('" + entry.getValue() + "','%Y-%m-%d')  ";
                } else if ("批发日期2".equals(entry.getKey())) {
                    sql += " AND date_format(aa.`批发日期`,'%Y-%m-%d') <=date_format('" + entry.getValue() + "','%Y-%m-%d')  ";
                } else if ("预计到港日期1".equals(entry.getKey())) {
                    sql += " AND date_format(aa.`预计到港日期（ETA）`,'%Y-%m-%d') >=date_format('" + entry.getValue() + "','%Y-%m-%d')  ";
                } else if ("预计到港日期2".equals(entry.getKey())) {
                    sql += " AND date_format(aa.`预计到港日期（ETA）`,'%Y-%m-%d') <=date_format('" + entry.getValue() + "','%Y-%m-%d')  ";
                } else if ("库龄天数1".equals(entry.getKey())) {
                    sql += " AND aa.`库龄天数` >='" + entry.getValue() + "'  ";
                } else if ("库龄天数2".equals(entry.getKey())) {
                    sql += " AND aa.`库龄天数` <='" + entry.getValue() + "'  ";
                }


                if ("car_state".equals(entry.getKey())) {
                    if (entry.getValue() == null || "".equals(entry.getValue())) {
                        sql += "  and (aa.car_state is null  or aa.car_state ='00')   ";
                    } else {
                        if ("00".equals(entry.getValue())) {
                            sql += "  and (aa.car_state is null  or aa.car_state ='00')   ";
                        } else {
                            sql += "and  aa.car_state ='01' ";
                        }
                    }
                }
            }

            if(1==1){

            }
            sql += " ORDER BY aa.`批发日期` DESC, aa.`生产日期` DESC, aa.`生产号` DESC";
            PageData<Map<String, Object>> pageData = this.getPage(sql, params, request.getPageSize(), request.getPageIndex());

            return pageData;
        } catch (Exception exc) {
            logger.error("Query Exception.", exc);
            return null;
        } finally {
            logger.warn("Time Ellapsed = " + (System.currentTimeMillis() - startTime));
        }
    }

    @Override
    public PageData getInfoByProudctionId(String pid,HttpServletRequest request) {
        long startTime = System.currentTimeMillis();

        try {
            String dealer= SessionInterceptor.getLicense(request);
            String sql = startOrderSql + " select " +
                    "   报价单表.报价编号," +
                    " `库存利润表`.`上报厂家`,`库存利润表`.`上报厂家日期`,`库存利润表`.`上报SIMS`,`库存利润表`.`上报SIMS日期`, " +
                    "  ROUND(`报价付款详细`.`应付金额`,2) `应收金额`, ROUND(`报价付款详细`.`实付金额`,2) `实收金额`, " +
                    " `报价订单表`.`订金交付日期`, `报价订单表`.`退订金转账日期`, " +
                    " `报价订单表`.`订金加减金额` `订金+/-金额`, `报价订单表`.`订金加减日期` `订金+/-日期`, " +
                    " `报价五星延保`.`延保原价` `五星延保MSRP（含税）`, `报价五星延保`.`延保折后价` `五星延保实收金额（含税）`, " +
                    " `报价五星延保`.`延保原价` `五星延保成本（含税）`, " +
                    "  IFNULL(`报价金融表`.`金融手续费`,0) `金融手续费`, " +
                    " `LSH库存表a`.`库存编号`,`LSH库存表a`.`批发日期`,`LSH库存表a`.`车款年份`, "+
                    "  ifnull((select cast(厂家延保MSRP as decimal)   from 报价五星延保 where 延保编号 = 报价单表.延保编号), 0) as '厂家延保MSRP(含税)',  " +
                    "  ifnull((select cast(延保原价 as decimal)       from 报价五星延保 where 延保编号 = 报价单表.延保编号), 0) as '本店延保MSRP(含税)',  " +
                    "  ifnull((select cast(第三方延保MSRP as decimal)  from 报价五星延保 where 延保编号 = 报价单表.延保编号), 0) as '第三方延保MSRP(含税)',  " +
                    "  ifnull((select cast(厂家延保实收 as decimal)    from 报价五星延保 where 延保编号 = 报价单表.延保编号), 0) as '厂家延保实收金额(含税)',  " +
                    "  ifnull((select cast(延保折后价 as decimal)      from 报价五星延保 where 延保编号 = 报价单表.延保编号), 0) as '本店延保实收金额(含税)',  " +
                    "  ifnull((select cast(第三方延保实收 as decimal)   from 报价五星延保 where 延保编号 = 报价单表.延保编号), 0) as '第三方延保实收金额(含税)',  " +
                    "  ifnull((select cast(厂家延保MSRP as decimal)    from 报价五星延保 where 延保编号 = 报价单表.延保编号), 0) as '厂家延保成本(含税)',  " +
                    "  ifnull((select cast(延保原价 as decimal)        from 报价五星延保 where 延保编号 = 报价单表.延保编号), 0) as '本店延保成本(含税)',  " +
                    "  ifnull((select cast(第三方延保MSRP as decimal)  from 报价五星延保 where 延保编号 = 报价单表.延保编号), 0) as '第三方延保成本(含税)'  " +
                    " from `LSH库存表` `LSH库存表a` " +
                    " LEFT JOIN `库存利润表` ON `LSH库存表a`.`库存编号` = `库存利润表`.`库存编号`" +
                    " LEFT JOIN  " +
                    "( " +
                    " select a.* FROM" +
                    "( " +
                    " SELECT a.* , b.`订单状态` 订单状态" +
                    " FROM 报价单表 a , 报价订单表  b" +
                    " where A.许可=:dealer and a.订单编号=b.`订单编号` and 是否驳回 = '否'  order by  `报价编号` desc " +
                    " ) a group by a.初始报价单号 " +
                    " ) 报价单表 ON `报价单表`.`库存编号`=  `LSH库存表a`.库存编号" +
                    " and 报价单表.订单状态 in ('意向订单O','意向订单C','订单修改','订单C','订单O','合同O','合同C','开票O','开票C','换票C','换票O','退票O','合同修改','换票' ) " +
                    "  LEFT JOIN `报价付款详细` ON `报价单表`.`报价编号` = `报价付款详细`.`报价编号` AND `付款事由` = '总计'  " +
                    "  LEFT JOIN `报价五星延保` ON `报价单表`.`延保编号` = `报价五星延保`.`延保编号`  " +
                    "  LEFT JOIN  `报价金融表` ON `报价金融表`.`金融编号` = `报价单表`.`金融`  " +
                    "  LEFT JOIN `报价订单表` ON `报价单表`.`订单编号` = `报价订单表`.`订单编号`  " +
                    " where `LSH库存表a`.`生产号`=:pid " + endOrderSql;
            Map params = Maps.newHashMap();
            params.put("dealer", dealer);
            params.put("pid", pid);
            PageData<Map<String, Object>> pageData = this.getPage(sql, params, 5, 0);
            List<Map<String, Object>> dataes = pageData.getData();

            // 找到调整后的SI
            List siList = getAdjustedSi(dealer);
            // 找到调整后的其他费用
            List feeList = getAdjustedFee(dealer);

            double dValue = 0d;
            if (dataes != null) {
                for (Map<String, Object> data : dataes) {
                    //将查询中关于SNP部分进行分解
                    this.splitQuotationsnp(data);

                    //将查询中关于保险部分进行分解
                    this.splitQuotationInsu(data);

                    //将查询中关于费用部分进行分解
                    this.splitQuotationExpense(data);

                    //将查询中关于精品部分进行分解
                    this.splitQuotationChoicest(data);

                    // 对于每一辆车，去siList寻找相应的调整后SI，如果找到覆盖原KEY-VALUE，未找到保留原KEY-VALUE
                    for (Object o : siList) {
                        Map m = (Map) o;
                        if (data.get("库存编号").equals(m.get("stock_id"))) { // 库存编号匹配
                            Integer siId = (Integer) m.get("si_setting_id");
                            BigDecimal bdSiAmount = (BigDecimal) m.get("si_amount");
                            Double siAmount = bdSiAmount.doubleValue();
                            switch (siId) { // 根据不同的调整项设置值
                                case 10:
                                    data.put("厂家SI_保险实收", siAmount);
                                    break;
                                case 16:
                                    data.put("厂家SI_购置税实收", siAmount);
                                    break;
                                case 27:
                                    data.put("厂家SI_SSSC实收", siAmount);
                                    break;
                                case 18:
                                    data.put("厂家SI_旅游礼包实收", siAmount);
                                    break;
                                case 32:
                                    data.put("厂家SI_SD卡实收", siAmount);
                                    break;
                                case 9:
                                    data.put("厂家SI_加油卡实收", siAmount);
                                    break;
                                case 28:
                                    data.put("厂家SI_实物礼品实收", siAmount);
                                    break;
                                case 6:
                                    data.put("贴息金额", siAmount);
                                case 19:
                                    data.put("厂家SI_服务礼券实收", siAmount);
                                    break;
                                case 24:
                                    if (data.get("调入经销商").equals(data.get("所属经销商"))) { // 调过的车没有批发SI
                                        data.put("厂家批售SI", 0.00);
                                    } else {
                                        data.put("厂家批售SI", siAmount);
                                    }
                                    break;
                                default: // 其他的零售SI调整全部加起来
                                    data.put("厂家零售SI", (Double) data.get("厂家零售SI") + siAmount);
                            }
                        }
                    }

                    // 对于每一辆车，去feeList寻找相应的调整后费用，如果找到覆盖原KEY-VALUE，未找到保留原KEY-VALUE
                    for (Object o : feeList) {
                        Map m = (Map) o;
                        if (data.get("库存编号").equals(m.get("stock_code"))) { // 库存编号匹配
                            BigDecimal bdFiPoRe = m.get("finance_poundage_adjust_real") == null ? new BigDecimal("0.00") : (BigDecimal) m.get("finance_poundage_adjust_real");
                            data.put("金融手续费", bdFiPoRe.doubleValue());

                        }
                    }

                    //计算GP
                    Map<GPUtils.ParameterName, Double> gpParameters = new HashMap<>();
                    gpParameters.put(GPUtils.ParameterName.厂家批售SI, toDouble(data.get("厂家批售SI")));
                    gpParameters.put(GPUtils.ParameterName.厂家零售SI, toDouble(data.get("厂家零售SI")));
                    gpParameters.put(GPUtils.ParameterName.金融手续费, toDouble(data.get("金融手续费")));
                    gpParameters.put(GPUtils.ParameterName.上牌服务费, toDouble(data.get("上牌服务费")));
                    gpParameters.put(GPUtils.ParameterName.其他服务费, toDouble(data.get("其他服务费")));

                }
            }
            return pageData;
        } catch (Exception e) {
            e.printStackTrace();
            logger.error("查询失败: getInfoByProudctionId", e);
            return null;
        }
    }

    @Override
    public PageData getOrders(String dlr, SalesService.GetOrdersRequest req) {
        StringBuilder sb = new StringBuilder("select * from (").append(orderSql).append(
                " ,顾客记录汇总.`电话号码`  AS '客户电话' " +
                "from lsh库存表 库存表  \n" +
                "join 报价单表 报价单表 on 报价单表.库存编号 = 库存表.库存编号  \n" +
                "join 报价订单表 报价订单表 on 报价单表.订单编号 = 报价订单表.订单编号  \n" +
                "left join 报价交车表 报价交车表 on 报价单表.交车 = 报价交车表.编号  \n" +
                "join 报价付款详细 报价付款详细 on 报价单表.报价编号 = 报价付款详细.报价编号 and 付款事由 = '总计'  \n" +
                "join lsh车型 车型 on 报价单表.车型编号 = 车型.车型编号  \n" +
                "join (select max(报价编号) as maxqNum from 报价单表 where 是否还原点 = '是' and 是否驳回 = '否' group by 初始报价单号) a on 报价单表.报价编号 = a.maxqNum  \n" +
                "JOIN `顾客记录汇总`  顾客记录汇总 ON `报价单表`.`顾客编号`= `顾客记录汇总`.`顾客编号` "   +
                "left join (select min(账目编号) 账目编号,订单编号,账目日期 from 销售财务账目表 where 账目类型 = '收款' group by 订单编号 ) 销售财务账目 on 报价单表.初始报价单号 = 销售财务账目.订单编号 \n" +
                "left join (select min(salesadmin.id) id,quote.初始报价单号 初始报价单号 from sale_admin_car_resource_check salesadmin join 报价单表 quote on salesadmin.quotedpricecode = quote.报价编号 group by quote.初始报价单号) saleTemp on 报价单表.初始报价单号 = saleTemp.初始报价单号 \n" +
                "where 库存表.car_state = '00'  \n" +
                "and 报价单表.许可 = :dlr  \n  " +
                 endOrderSql +
                ") aa where 1 = 1  \n"
        );
        Map<String, Object> params = new HashMap<>();
        params.put("dlr", dlr);
        if (req.orderedUnsold) {
            sb.append("and aa.开票日期 is null  \n"); // 意向订单后处理生成的虚拟车状态为未卖，所以不能用车辆零售状态来过滤
        }
        if (req.custPhone != null && req.custPhone.length() != 0){
            sb.append(" AND aa.客户电话 like :custPhone ");
            params.put("custPhone","%" + req.custPhone + "%");
        }
        if (req.cus != null && req.cus.length() != 0) {
            sb.append("and aa.订单客户 like :cus  \n");
            params.put("cus", "%" + req.cus + "%");
        }
        if (req.sc != null && req.sc.length() != 0) {
            sb.append("and aa.销售顾问 like :sc  \n");
            params.put("sc", "%" + req.sc + "%");
        }
        if (req.prodNum != null && req.prodNum.length() != 0) {
            sb.append("and 生产号 like :prodNum  \n");
            params.put("prodNum", "%" + req.prodNum + "%");
        }
        if (req.orderDtStart != null) {
            sb.append("and aa.订单生效日期 >= :orderDtStart  \n");
            params.put("orderDtStart", req.orderDtStart);
        }
        if (req.orderDtEnd != null) {
            sb.append("and aa.订单生效日期 <= :orderDtEnd  \n");
            params.put("orderDtEnd", req.orderDtEnd);
        }
        if (req.vin != null && req.vin.length() != 0) {
            sb.append("and aa.`车架号（VIN）` like :vin  \n");
            params.put("vin", "%" + req.vin + "%");
        }
        if (req.exOrderDtStart != null) {
            sb.append("and aa.意向订单生效日期 >= :exOrderDtStart  \n");
            params.put("exOrderDtStart", req.exOrderDtStart);
        }
        if (req.exOrderDtEnd != null) {
            sb.append("and aa.意向订单生效日期 <= :exOrderDtEnd  \n");
            params.put("exOrderDtEnd", req.exOrderDtEnd);
        }
        if (req.status != null && req.status.length() != 0) {
            sb.append("and aa.新车销售状态 = :status  \n");
            params.put("status", req.status);
        }
        if (req.invoiceDtStart != null) {
            sb.append("and aa.开票日期 >= :invoiceDtStart  \n");
            params.put("invoiceDtStart", req.invoiceDtStart);
        }
        if (req.invoiceDtEnd != null) {
            sb.append("and aa.开票日期 <= :invoiceDtEnd  \n");
            params.put("invoiceDtEnd", req.invoiceDtEnd);
        }
        if (req.deliverDtStart != null) {
            sb.append("and aa.交车日期 >= :deliverDtStart  \n");
            params.put("deliverDtStart", req.deliverDtStart);
        }
        if (req.deliverDtEnd != null) {
            sb.append("and aa.交车日期 <= :deliverDtEnd  \n");
            params.put("deliverDtEnd", req.deliverDtEnd);
        }
        if (req.brand != null && req.brand.length() != 0) {
            sb.append("and aa.品牌 = :brand  \n");
            params.put("brand", req.brand);
        }
        if (req.clazz != null && req.clazz.length() != 0) {
            sb.append("and concat(if(aa.来源 = 'CBU', '', concat(aa.来源, ' ')), aa.车系) = :clazz  \n");
            params.put("clazz", req.clazz);
        }
        if (req.model != null && req.model.length() != 0) {
            sb.append("and aa.车型 = :model  \n");
            params.put("model", req.model);
        }
        if (req.modelDetail != null && req.modelDetail.length() != 0) {
            sb.append("and aa.车款 = :modelDetail  \n");
            params.put("modelDetail", req.modelDetail);
        }
        return this.getPage(sb.toString(), params, req.getPageSize(), req.getPageIndex());
    }

    @Override
    public List getOrdersHistory(String originId) {
        StringBuilder sb = new StringBuilder(orderSql).append("from lsh库存表 库存表  \n" +
                "join (" +
                "    select a.* from 报价单表 a,(select 报价编号 from 报价单表 where 初始报价单号 = :originId and 是否还原点 = '是' and 是否驳回 = '否' order by 报价编号 desc limit 1) b where a.报价编号 = b.报价编号  " +
                "    union ALL " +
                "    select * from 报价单表 where carTypeChange = '1' and 初始报价单号 = :originId " +
                "  ) 报价单表 on 报价单表.库存编号 = 库存表.库存编号  \n" +
                "left join 报价订单表 报价订单表 on 报价单表.订单编号 = 报价订单表.订单编号  \n" +
                "left join 报价交车表 报价交车表 on 报价单表.交车 = 报价交车表.编号  \n" +
                "join 报价付款详细 报价付款详细 on 报价单表.报价编号 = 报价付款详细.报价编号 and 付款事由 = '总计'  \n" +
                "join lsh车型 车型 on 报价单表.车型编号 = 车型.车型编号  \n" +
                "left join (select min(账目编号) 账目编号,订单编号,账目日期 from 销售财务账目表 where 账目类型 = '收款' group by 订单编号 ) 销售财务账目 on 报价单表.初始报价单号 = 销售财务账目.订单编号\n" +
                "left join (select min(salesadmin.id) id,quote.初始报价单号 初始报价单号 from sale_admin_car_resource_check salesadmin join 报价单表 quote on salesadmin.quotedpricecode = quote.报价编号 group by quote.初始报价单号) saleTemp on 报价单表.初始报价单号 = saleTemp.初始报价单号\n" +
                "where 库存表.car_state = '00'  \n" +
                "order by 报价单表.报价编号 asc \n"
        );
        sb.append(endOrderSql);
        Map<String, Object> params = new HashMap<>();
        params.put("originId", originId);
        return this.getList(sb.toString(), params);
    }

    @Override
    public List getOrdersReview(Integer qNum) {
        String sql = startOrderSql + "select  \n" +
                "  b.生产号 as '生产号',  \n" +
                "  b.车架号 as '车架号',  \n" +
                "  b.车款年份 as '车款年份',  \n" +
                "  (select 品牌     from lsh车型 where 车型编号 = b.车型编号) as '品牌',  \n" +
                "  (select 级别     from lsh车型 where 车型编号 = b.车型编号) as '车系',  \n" +
                "  (select 车型     from lsh车型 where 车型编号 = b.车型编号) as '车型',  \n" +
                "  (select 来源     from lsh车型 where 车型编号 = b.车型编号) as '来源',  \n" +
                "  (select 车型详细 from lsh车型 where 车型编号 = b.车型编号) as '车款',  \n" +
                "  a.外观 as '外观',  \n" +
                "  b.生产日期 as '生产日期',  \n" +
                "  b.车辆物流状态 as '车辆物流状态',  \n" +
                "  b.批发日期 as '批发日期',  \n" +
                "  b.批发付款到期日 as '批发付款到期日',  \n" +
                "  b.hold日期 as 'HOLD日期',  \n" +
                "  b.预计到店日期 as '预计到店日期',  \n" +
                "  b.入库日期 as '入库日期',  \n" +
                "  b.申请释放日期 as '申请释放日期',  \n" +
                "  b.店内车辆位置 as '车辆位置',  \n" +
                "  b.车辆零售状态 as '车辆零售状态',  \n" +
                "  b.nbi车辆状态 as 'NBI车辆状态',  \n" +
                "  b.二次pdi结果 as '二次PDI结果',  \n" +
                "  b.建议三次pdi日期 as '建议三次PDI日期',  \n" +
                "  b.三次pdi结果 as '三次PDI结果',  \n" +
                "  (select 上报厂家 from 库存利润表 where 库存编号 = b.库存编号) as '上报厂家',  \n" +
                "  (select 上报sims from 库存利润表 where 库存编号 = b.库存编号) as '上报SIMS',  \n" +
                "  b.三次pdi完成日期 as '三次PDI日期',  \n" +
                "  (select 上报厂家日期 from 库存利润表 where 库存编号 = b.库存编号) as '上报厂家日期',  \n" +
                "  (select 上报sims日期 from 库存利润表 where 库存编号 = b.库存编号) as '上报SIMS日期',  \n" +
                "  b.车辆受损情况 as '车辆受损情况',  \n" +
                "  b.关单号 as '关单到店日期',  \n" +
                "  b.商检单号 as '商检单到店日期',  \n" +
                "  b.合格证号 as '合格证号',  \n" +
                "  ROUND(c.实付金额, 2) as '实收金额',  \n" +
                "  (select 订金交付日期 from 报价订单表 where 订单编号 = a.订单编号) as '订金交付日期',  \n" +
                "  (select 退订金转账日期 from 报价订单表 where 订单编号 = a.订单编号) as '退订金转账日期',  \n" +
                "  (select 订金加减金额 from 报价订单表 where 订单编号 = a.订单编号) as '订金+/-金额',  \n" +
                "  (select 订金加减日期 from 报价订单表 where 订单编号 = a.订单编号) as '订金+/-日期',  \n" +
                "  b.出库日期 as '出库日期',  \n" +
                "  b.采购批发商 as '采购经销商',  \n" +
                "  b.协商调车款项金额 as '协商调车金额',  \n" +
//                "  (select 延保原价   from 报价五星延保 where 延保编号 = a.延保编号) as '五星延保MSRP(含税)',  \n" +
//                "  (select 延保折后价 from 报价五星延保 where 延保编号 = a.延保编号) as '五星延保实收金额(含税)',  \n" +
//                "  (select 延保原价   from 报价五星延保 where 延保编号 = a.延保编号) as '五星延保成本(含税)',  \n" +
                "  ifnull((select concat(cast(厂家延保MSRP as decimal(17, 2)), '')   from 报价五星延保 where 延保编号 = a.延保编号), '0.00') as '厂家延保MSRP(含税)',  \n" +
                "  ifnull((select concat(cast(延保原价 as decimal(17, 2)), '')       from 报价五星延保 where 延保编号 = a.延保编号), '0.00') as '本店延保MSRP(含税)',  \n" +
                "  ifnull((select concat(cast(第三方延保MSRP as decimal(17, 2)), '')  from 报价五星延保 where 延保编号 = a.延保编号), '0.00') as '第三方延保MSRP(含税)',  \n" +
                "  ifnull((select concat(cast(厂家延保实收 as decimal(17, 2)), '')    from 报价五星延保 where 延保编号 = a.延保编号), '0.00') as '厂家延保实收金额(含税)',  \n" +
                "  ifnull((select concat(cast(延保折后价 as decimal(17, 2)), '')      from 报价五星延保 where 延保编号 = a.延保编号), '0.00') as '本店延保实收金额(含税)',  \n" +
                "  ifnull((select concat(cast(第三方延保实收 as decimal(17, 2)), '')   from 报价五星延保 where 延保编号 = a.延保编号), '0.00') as '第三方延保实收金额(含税)',  \n" +
                "  ifnull((select concat(cast(厂家延保MSRP as decimal(17, 2)), '')    from 报价五星延保 where 延保编号 = a.延保编号), '0.00') as '厂家延保成本(含税)',  \n" +
                "  ifnull((select concat(cast(延保原价 as decimal(17, 2)), '')        from 报价五星延保 where 延保编号 = a.延保编号), '0.00') as '本店延保成本(含税)',  \n" +
                "  ifnull((select concat(cast(第三方延保MSRP as decimal(17, 2)), '')  from 报价五星延保 where 延保编号 = a.延保编号), '0.00') as '第三方延保成本(含税)',  \n" +
                "  ifnull((select if(金融手续费 = '', '0.00', concat(cast(金融手续费 as decimal(17, 2)), '')) from 报价金融表 where 金融编号 = a.金融), '0.00') as '金融手续费',  \n" +
                "  ifnull((select if(费用折后价 = '', '0.00', concat(cast(费用折后价 as decimal(17, 2)), '')) from 报价费用详细表 where 费用编号 = a.费用编号 and 费用名称 = '上牌服务费'), '0.00') as '上牌服务费',  \n" +
                "  ifnull((select if(费用折后价 = '', '0.00', concat(cast(费用折后价 as decimal(17, 2)), '')) from 报价费用详细表 where 费用编号 = a.费用编号 and 费用名称 = '其他服务费'), '0.00') as '其他服务费',  \n" +

                "  a.报价编号  \n" +
                "from 报价单表 a  \n" +
                "join lsh库存表 b on a.库存编号 = b.库存编号  \n" +
                "join 报价付款详细 c on a.报价编号 = c.报价编号 and 付款事由 = '总计'  \n" +
                "where a.报价编号 = :qNum  \n" + endOrderSql;
        Map<String, Object> params = new HashMap<>();
        params.put("qNum", qNum);
        return this.getList(sql, params);
    }

    public List getOrdersByAllOriginId(String dealer, Map inputParam, RequestBase request) {
        StringBuilder sb = new StringBuilder(orderSql).append(",顾客记录汇总.`电话号码`  AS '客户电话' from lsh库存表 库存表  \n" +
//                "join 报价单表 报价单表 on 报价单表.库存编号 = 库存表.库存编号  \n" +
                "join (" +
                "    select a.* from 报价单表 a,(select 报价编号 from (SELECT 报价编号,初始报价单号 FROM 报价单表 WHERE 是否还原点 = '是' AND 是否驳回 = '否' ORDER BY 报价编号 desc ) c group by c.初始报价单号) b where a.报价编号 = b.报价编号  " +
                "    union ALL " +
                "    select * from 报价单表 where carTypeChange = '1'  " +
                "  ) 报价单表 on 报价单表.库存编号 = 库存表.库存编号  \n" +
                "left join 报价订单表 报价订单表 on 报价单表.订单编号 = 报价订单表.订单编号  \n" +
                "left join 报价交车表 报价交车表 on 报价单表.交车 = 报价交车表.编号  \n" +
                "join 报价付款详细 报价付款详细 on 报价单表.报价编号 = 报价付款详细.报价编号 and 付款事由 = '总计'  \n" +
                "join lsh车型 车型 on 报价单表.车型编号 = 车型.车型编号  \n" +
                "JOIN `顾客记录汇总`  顾客记录汇总 ON `报价单表`.`顾客编号`= `顾客记录汇总`.`顾客编号`  \n"+
                "left join (select min(账目编号) 账目编号,订单编号,账目日期 from 销售财务账目表 where 账目类型 = '收款' group by 订单编号 ) 销售财务账目 on 报价单表.初始报价单号 = 销售财务账目.订单编号 \n" +
                "left join (select min(salesadmin.id) id,quote.初始报价单号 初始报价单号 from sale_admin_car_resource_check salesadmin join 报价单表 quote on salesadmin.quotedpricecode = quote.报价编号 group by quote.初始报价单号) saleTemp on 报价单表.初始报价单号 = saleTemp.初始报价单号 \n" +
                "where 库存表.car_state = '00'  \n"
//                "and 报价单表.是否还原点 = '是'  \n" +
//                "and 报价单表.是否驳回 = '否'  \n"
        );
        Map<String, Object> params = new HashMap<>();
        String allOriginId = (String) inputParam.get("allOriginId");
        String[] originIds = allOriginId.split("[|]");
//        if (request.getPageSize() == 0 && request.getPageIndex() == 0) { // 如果要求导出全部页，只要过滤许可
//            sb.append("and 报价单表.许可 = :dealer  \n");
//            params.put("dealer", dealer);
//            PageData pageData = this.getPage(sb.toString(), params, request.getPageSize(), request.getPageIndex());
//            if (pageData == null) {
//                return new ArrayList();
//            } else {
//                return pageData.getData();
//            }
//        } else { // 导出当前页时
        if (!Strings.isNullOrEmpty(originIds[0])) {
            sb.append("and (  \n");
            for (int i = 0; i < originIds.length; i++) {
                if (i == 0) {
                    sb.append("报价单表.初始报价单号 = :originId").append(i).append("  \n");
                } else {
                    sb.append("or 报价单表.初始报价单号 = :originId").append(i).append("  \n");
                }
                params.put("originId" + i, originIds[i]);
            }
            sb.append(")  \n");
        }
        sb.append(endOrderSql);
            return this.getList(sb.toString(), params);
//        }
    }
}
